Examples of using dates as criteria in Access queries

Applies to: Access for Microsoft 365, Access 2019 Access 2016, Access 2013, Access 2010, Access 2007.

To explore further about creating queries, see Introduction to queries.

Here are some regular date criteria examples, spanning from basic date filters to deeply complex date range calculations. Some of the more complex examples use Access date functions to gather various sections of a date to assist you with acquiring only your desired results.

Examples that use the current date in their criteria

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

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

Examples that use the current date in their criteria

To include items that …Use this criteriaQuery result
Contain today’s dateDate()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 dateDate()-1Returns items with yesterday’s date. If today’s date is 2/2/2012, you’ll see items for Feb 1, 2012.
Contain tomorrow’s dateDate() + 1Returns 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 weekDatePart(“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 weekYear([SalesDate])* 53 + DatePart(“ww”, [SalesDate]) = Year(Date())* 53 + DatePart(“ww”, Date()) – 1Returns items with dates during the last week. A week in Access starts on Sunday and ends on Saturday.
Contain dates within the following weekYear([SalesDate])* 53+DatePart(“ww”, [SalesDate]) = Year(Date())* 53+DatePart(“ww”, Date()) + 1Returns items with dates during next week. A week in Access starts on Sunday and ends on Saturday.
Contain a date within the last 7 daysBetween Date() and Date()-6Returns 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 monthYear([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 monthYear([SalesDate])* 12 + DatePart(“m”, [SalesDate]) = Year(Date())* 12 + DatePart(“m”, Date()) – 1Returns 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 monthYear([SalesDate])* 12 + DatePart(“m”, [SalesDate]) = Year(Date())* 12 + DatePart(“m”, Date()) + 1Returns 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 daysBetween 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 quarterYear([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 quarterYear([SalesDate])*4+DatePart(“q”,[SalesDate]) = Year(Date())*4+DatePart(“q”,Date())- 1Returns 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 quarterYear([SalesDate])*4+DatePart(“q”,[SalesDate]) = Year(Date())*4+DatePart(“q”,Date())+1Returns 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 yearYear([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 yearYear([SalesDate]) = Year(Date()) – 1Returns 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 yearYear([SalesDate]) = Year(Date()) + 1Returns 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 criteriaQuery 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/2012Not #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 datesIn (#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 DecemberDatePart(“m”, [SalesDate]) = 12Returns items with a date in December of any year.
Contain a date within a specific quarter (regardless of year), such as the first quarterDatePart(“q”, [SalesDate]) = 1Returns items with a date in the first quarter of any year.
Filter for null (or missing) valuesIs NullReturns items where the date has not been entered.
Filter for non-null valuesIs Not NullReturns items where the date has been entered.

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

To include items that …Use this criteriaQuery result
Filter for null (or missing) valuesIs NullReturns items where the date has not been entered.
Filter for non-null valuesIs Not NullReturns items where the date has been entered.

Having difficulty with date criteria, such as getting different results from what you expect? See Date criteria doesn’t work in my query.

Leave a Reply

%d bloggers like this: