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.
Examples of using dates as criteria in Access queries

To learn about creating queries, see Introduction to queries.

Here are some common date criteria examples, ranging from simple date filters to more complex date range calculations. Some of the more complex examples use Access date functions to extract different parts of a date to help you get just the results you want.

Examples that use the current date in their criteria

To include items that ...

Use this criteria

Query result

Contain today's date

Date()

Returns items with a date of today. If today's date is 2/2/2012, you’ll see items where the date field is set to Feb 2, 2012.

Contain yesterday's date

Date()-1

Returns items with yesterday’s date. If today's date is 2/2/2012, you’ll see items for Feb 1, 2012.

Contain tomorrow's date

Date() + 1

Returns items with tomorrow’s date. If today's date is Feb 2, 2012, you’ll see items for Feb 3, 2012.

Contain dates within the current week

DatePart("ww", [SalesDate]) = DatePart("ww", Date()) and Year( [SalesDate]) = Year(Date())

Returns items with dates during the current week. A week in Access starts on Sunday and ends on Saturday.

Contain dates within the previous week

Year([SalesDate])* 53 + DatePart("ww", [SalesDate]) = Year(Date())* 53 + DatePart("ww", Date()) - 1

Returns items with dates during the last week. A week in Access starts on Sunday and ends on Saturday.

Contain dates within the following week

Year([SalesDate])* 53+DatePart("ww", [SalesDate]) = Year(Date())* 53+DatePart("ww", Date()) + 1

Returns items with dates during next week. A week in Access starts on Sunday and ends on Saturday.

Contain a date within the last 7 days

Between Date() and Date()-6

Returns items with dates during the last 7 days. If today's date is 2/2/2012, you’ll see items for the period Jan 24, 2012 through Feb 2, 2012.

Contain a date within the current month

Year([SalesDate]) = Year(Now()) And Month([SalesDate]) = Month(Now())

Returns items with dates in the current month. If today's date is 2/2/2012, you’ll see items for Feb 2012.

Contain a date within the previous month

Year([SalesDate])* 12 + DatePart("m", [SalesDate]) = Year(Date())* 12 + DatePart("m", Date()) - 1

Returns items with dates in the previous month. If today's date is 2/2/2012, you’ll see items for Jan 2012.

Contain a date within the next month

Year([SalesDate])* 12 + DatePart("m", [SalesDate]) = Year(Date())* 12 + DatePart("m", Date()) + 1

Returns items with dates in the next month. If today's date is 2/2/2012, you’ll see items for Mar 2012.

Contain a date within the last 30 or 31 days

Between Date( ) And DateAdd("M", -1, Date( ))

Returns a month's worth of items. If today's date is 2/2/2012, you’ll see items for the period Jan 2, 2012 to Feb 2, 2012.

Contain a date within the current quarter

Year([SalesDate]) = Year(Now()) And DatePart("q", [SalesDate]) = DatePart("q", Now())

Returns items for the current quarter. If today's date is 2/2/2012, you’ll see items for the first quarter of 2012.

Contain a date within the previous quarter

Year([SalesDate])*4+DatePart("q",[SalesDate]) = Year(Date())*4+DatePart("q",Date())- 1

Returns items for the previous quarter. If today's date is 2/2/2012, you’ll see items for the last quarter of 2011.

Contain a date within the next quarter

Year([SalesDate])*4+DatePart("q",[SalesDate]) = Year(Date())*4+DatePart("q",Date())+1

Returns items for the next quarter. If today's date is 2/2/2012, you’ll see items for the second quarter of 2012.

Contain a date within the current year

Year([SalesDate]) = Year(Date())

Returns items for the current year. If today's date is 2/2/2012, you’ll see items for the year 2012.

Contain a date within the previous year

Year([SalesDate]) = Year(Date()) - 1

Returns items for the previous year. If today's date is 2/2/2012, you’ll see items for the year 2011.

Contain a date within the next year

Year([SalesDate]) = Year(Date()) + 1

Returns items with next year's date. If today's date is 2/2/2012, you’ll see items for the year 2013.

Contain a date between Jan 1 and today (year-to-date items)

Year([SalesDate]) = Year(Date()) and Month([SalesDate]) <= Month(Date()) and Day([SalesDate]) <= Day (Date())

Returns items with dates between Jan 1 of the current year and today. If today's date is 2/2/2012, you’ll see items for the period Jan 1, 2012 to 2/2/2012.

Contain a date that occurred in the past

< Date()

Returns items with dates before today.

Contain a date that occurs in the future

> Date()

Returns items with dates after today.

Examples that work with a date or range of dates other than the current date

To include items that ...

Use this criteria

Query result

Exactly match a date, such as 2/2/2012

#2/2/2012#

Returns only items with a date of Feb 2, 2012.

Do not match a date, such as 2/2/2012

Not #2/2/2012#

Returns items with a date other than Feb 2, 2012.

Contain values before a certain date, such as 2/2/2012

< #2/2/2012#

Returns items with a date before Feb 2, 2012.

To view items with a date on or before Feb 2, 2012, use the <= operator instead of the < operator.

Contain values after a certain date, such as 2/2/2012

> #2/2/2012#

Returns items with a date after Feb 2, 2012.

To view items with a date on or after Feb 2, 2012, use the >= operator instead of the > operator.

Contain values within a date range (between two dates)

>#2/2/2012# and <#2/4/2012#

Returns items with a date between Feb 2, 2012 and Feb 4, 2012.

Note: You can also use the Between operator to filter for a range of values, including the end points. For example, Between #2/2/2012# and #2/4/2012# is the same as >=#2/2/2012# and <=#2/4/2012#.

Contain dates outside a range

<#2/2/2012# or >#2/4/2012#

Returns items with a date before Feb 2, 2012 or after Feb 4, 2012.

Contain one of two dates, such as 2/2/2012 or 2/3/2012

#2/2/2012# or #2/3/2012#

Returns items with a date of either Feb 2, 2012 or Feb 3, 2012.

Contain one or more of many dates

In (#2/1/2012#, #3/1/2012#, #4/1/2012#)

Returns items with a date of Feb 1, 2012, March 1, 2012, or April 1, 2012.

Contain a date within a specific month (regardless of year), such as December

DatePart("m", [SalesDate]) = 12

Returns items with a date in December of any year.

Contain a date within a specific quarter (regardless of year), such as the first quarter

DatePart("q", [SalesDate]) = 1

Returns items with a date in the first quarter of any year.

Filter for null (or missing) values

Is Null

Returns items where the date has not been entered.

Filter for non-null values

Is Not Null

Returns items where the date has been entered.

Examples that filter for null (missing) or non-null dates

To include items that ...

Use this criteria

Query result

Filter for null (or missing) values

Is Null

Returns items where the date has not been entered.

Filter for non-null values

Is Not Null

Returns items where the date has been entered.

Having trouble with date criteria, such as not getting the results you expect? See Date criteria doesn't work in my query.

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!

×