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. |
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.
No comments:
Post a Comment