Access Query Date Criteria Examples

On my Contextures website, the focus in on Excel tips and tutorials. Behind the scenes, I use Microsoft Access too, for time tracking, website statistics and a few other key tasks. This week, I wanted to find files that hadn’t been updated for a long time, so I created a query that selects records older than X – Access asks what X is each time the query runs. Here’s what I built, and a few other Access query date criteria examples.

Access Query With Simple Date Criteria

If there is a date field in a table, it’s easy to build a simple query that selects record before or after a specific date. For example, use this criterion in the date field, to select records that were revised on or before January 1, 2017.

<= 1/1/2017

Access automatically puts number signs before and after the date, when you  press Enter, or click away from the criteria cell

<= #1/1/2017#

accessquerydates01

Access Query Criteria for Specific Date Range

To select records within a specific date range, with two dates with the AND operator, in the query criteria. Here’s the criteria string that selects records from January 1, 2017 to May 31, 2017 (including those dates).

Between #1/1/2017# And #5/31/2017#

accessquerydates02

Query With Changeable Dates

Just like an Excel date formula, it’s usually better to have a variable in an Access query, instead of hard coding a specific date. In Excel, a formula could refer to a worksheet cell, and a date could be entered there, and changed easily.

In Access, you could have a text box on a form, and enter a date there. Then, refer to that text box in the Access query date criteria.

Here is a form where I can enter the start and end dates, and then run a series of reports based on those dates. It’s easy to change the dates before running the reports, and very efficient for doing month end summaries.

accessquerydates04

In the queries that the reports are based on, the date criteria refer to those text boxes.

Between [forms]![Menu].[txtStart] And [forms]![Menu].[txtEnd]

accessquerydates05

Query With Prompt for Dates

If you’re not running a series of reports from an Access form, it’s easier to use parameters in your query criteria. The parameters will prompt you to enter the start and end dates, or other criteria that you want to have as variables.

So, to select old records with a query, I can put a parameter in my original query, replacing the date. The Parameter is enclosed in square brackets.

<= [Before what date?]

When the query runs, the parameter will appear in a pop up message. Type a date in the input box, and click OK, to see the results.

Access Query Date Criteria Examples

More Access Query Date Criteria Examples

If you don’t want to enter the entire date (that can get tiring!), use a parameter as part of a date. In the next example, the DateSerial function is used in the criteria, and the parameter prompts you for the year.

The month (1) and day (1) are hard coded – the query will select everything before January 1st of the year that you enter.

<DateSerial([Before what year?],1,1)

accessquerydates07

How Many Years Old?

Maybe you would rather focus on the record age, instead of a specific year. In this example, I used the DateAdd function, with year as the interval, then a minus sign, and a prompt for how old the files should be. The Date function at the end will base the calculation on the current date.

<=DateAdd(“yyyy”,-[How many years old?],Date())

When you run the query, enter a number in the input box, and the query will select records that are on or before that date – X years before the current date.

accessquerydates08

__________________

Save

This entry was posted in Microsoft Office Tips. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *