Date criteria doesn’t work in my query

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.

Date criteria that won't return the right results

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:

This date criteria will work

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:

CriteriaReturns records with:
>12/31/10Dates on or after 1/1/2011.
<=6/1/2014Dates on or before 6/1/2014.
8/25/13The only the date 8/25/13.
Between 9/1 And 12/31/15Dates on or after 9/1 of the current year and on or before 12/31/15.
>3/31/13<7/1/13All dates. The criteria is missing the “And” operator, so it doesn’t filter any results out.
Between 3/1/10 And 1/1/05Dates 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/d10/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.

Leave a Reply

%d bloggers like this: