Date criteria doesn't work in my query When you use a date as criteria in an Access query, you might find t...

Date criteria doesn't work in my query

When you use a date as criteria in an Access query, you might find that you don't get the results you expect.

In the query shown above, the criteria >=#1/1/2005#<#1/1/2010# won't work because it's not valid. The query runs, but it's likely the results will be wrong. The criteria needs the "And" operator between the dates. The criteria shown below, with the "And" operator, will work as expected:

To make things easier, instead of using the "greater than," "less than," or "equal to" signs, you can use "Between" with the dates and the "And" operator to do the same test, like this:

Between #1/1/2005# And #1/1/2010#

Notice the # characters (pound signs) that surround the dates? When you enter a date that Access recognizes, it automatically gets enclosed in # characters.

Examples of date criteria

This shows 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 recognizes

Here are some of the many date formats you can use in the Criteria cell:

Format	Example
m/d/yyyy	10/5/2013
m/d	10/5 Access uses the current year if you don't specify one.
d-mmm-yyyy	5-Oct-2013
m-d-yyyy	3-7-1990

See Examples of using dates as criteria in Access queries for many ways to use dates as query criteria.