Sign in with Microsoft
Sign in or create an account.
Hello,
Select a different account.
You have multiple accounts
Choose the account you want to sign in with.
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.

Date criteria that won't return the right results

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:

This date criteria will work

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.

Need more help?

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

Was this information helpful?

What affected your experience?
By pressing submit, your feedback will be used to improve Microsoft products and services. Your IT admin will be able to collect this data. Privacy Statement.

Thank you for your feedback!

×