Applies to: Access for Microsoft 365, Access 2019, Access 2016, Access 2013, Access 2010, Access 2007.
After you apply a date as criteria in an Access query, you may discover that you reach less than desired results from you anticipated.
In the query displayed above, the criteria >=#1/1/2005#<#1/1/2010# will be problematic since it’s invalid. The query preforms, but it’s probable that the results will be inaccurate. The criteria requires the “And” operator between the dates. The criteria illustrated below, with the “And” operator, will work as expected:
To simplify matters, rather than using the “greater than,” “less than,” or “equal to” signs, you can employ “Between” with the dates and the “And” operator to run the exact test, like this:
Between #1/1/2005# And #1/1/2010#
Observe the # characters (pound signs) that enclose the dates? After you type a date that is familiar to Access, it promptly gets enveloped in # characters.
Examples of date criteria
This presents some criteria that will work, and one that won’t:
|Criteria||Returns records with:|
|>12/31/10||Dates on or after 1/1/2011.|
|<=6/1/2014||Dates on or before 6/1/2014.|
|8/25/13||The only the date 8/25/13.|
|Between 9/1 And 12/31/15||Dates on or after 9/1 of the current year and on or before 12/31/15.|
|>3/31/13<7/1/13||All dates. The criteria is missing the “And” operator, so it doesn’t filter any results out.|
|Between 3/1/10 And 1/1/05||Dates on or after 1/1/05 and on or before 3/1/10. It doesn’t matter that the later date is entered in the criteria before the earlier date.|
Examples of date formats that Access recognises
Here are some of the many date formats you can use in the Criteria cell:
|m/d||10/5Access uses the current year if you don’t specify one.|
Read Examples of using dates as criteria in Access queries for various ways to employ dates as query criteria.