databasedev.co.uk - database solutions and downloads for microsoft access

Easily retrieve data for the Current Month or Year in a Microsoft Access Query

If you need to limit Microsoft Access 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.

The following examples will show the results that relate to the information stored in the Orders table of a Microsoft Access database. The original database table is shown below:

Microsoft Access table showing records held in the Orders table

For instance, say that you have a database 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())

The query design should look like the following:

Microsoft Access Query design to query for records in the current Year

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.)

The following results are returned from the sample table, where they only match the criteria of records that fall in the current Year:

Records that match the current year criteria of the Microsoft Access query

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 design should look like the following:

Microsoft Access Query design to query for records in the current Month

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

The following results are returned from the sample table, where they only match the criteria of records that fall in the current Month:

Records that match the current month criteria of the Microsoft Access query