Creating a Period Date Range

[Legacy 15162]

I am creating a database for my company and we run off an abnormal period. We have 13 periods for the year. Our weeks run from Tues-Monday. I am trying to put together a DB that shows our weekly sales by period. Currently, as of 2/11/04, we are on Period 2 Week 4. The data comes in everynight and we report based on yesterday's data. How can I get the queries to show only that data for the current period without having to enter a date range everytime?

 

[Dan Aragon]

One way might be to create a table with three fields. First field would be the period number (fldPeriod), the second field would be the starting date of the period (fldStartDate), and the third field would be the ending date of the period (fldEndDate).

Add the table to your query, but don't make any relationships between the two.

Bring in the fldPeriod field from your new table in to the query and for Criteria enter:
=[Enter Period]

To pull in the correct dates, you would just enter this criteria in the date field of your daily report table:
Between [fldStartDate] and [fldEndDate]

This should work, but I haven't tested it. When you run the query it will prompt you to enter the Period and it should pull the correct dates.