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", Date()) = 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.

Queries 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.

 
 
Applies to:
Access 2013