Microsoft Access - Easily Retrieve Data For The Current Month Or Year (Access 97/2000/2002)

If you need to limit query results to a particular month or year, you may not have to specify exact beginning and ending dates when establishing your criteria, particularly if the selection criteria are relative to the current date. Instead, you can simplify things using the Month() and Year () functions.

For instance, say that you have a table containing the field PurchaseDate. In the query design grid, add the following expression to a blank Field text box:

  • PurchaseYear: Year([PurchaseDate])

and enter the following in the column's Criteria text box:

  • Year(Now())

Running the query displays all of the records with a PurchaseDate value that falls within the current year. (To hide the year column, just clear the PurchaseYear column's Show checkbox when the query is open in Design view.)

Likewise, you can find records made in a particular month using the Month() function. Say you keep the PurchaseYear criteria in place and add a field that uses the expression:

  • PurchaseMonth: Month([PurchaseDate])

If you set PurchaseMonth's criteria to:

  • Month(Now())

the query returns records with a PurchaseDate value from the current month of the current year.

Go back