ZOOMING IN ON DATE RANGES

Access offers a number of useful date functions you can use to create expressions to isolate records with date stamps that fall within a particular period of calendar time.
Of course, you can always hard-code your expressions with the actual dates involved or write a routine that prompts the user for a beginning and ending date.
But if your users routinely run queries that are relative to the current date, here are six expressions you can use, based on a specific date field in your table, to locate records from a given period of time.
* To find all records date-stamped in the previous seven days (excluding today), try this expression: Between Date()-1 And Date()-7
* To access all records date-stamped last week, use this: Between Date()-6-WeekDay(Date()) And Date()-WeekDay(Date())
* To locate all records from the current month, try this expression: Month([myDateField])=Month(Date()) And Year([myDateField])=Year(Date())
* To access all records from the previous month, use this: Month([myDateField]) =IIf(Month(Date())=1, 12, Month(Date())-1) And Year([myDateField]) =IIf(Month(Date())=1, Year([myDateField])- 1,Year(Date())
* To locate all records from the current year, try this expression: Year([myDateField])=Year(Date())
* To find all records from the previous year, use this: Year([myDateField])=Year(Date())-1 In the expression to find records from a previous month, we used the "immediate if" function to set the month value to 12 when the current month is January. In all other cases, we simply subtract one from the current month.
We included the And clause so that, when the current month is January, we also subtract one from the current year.