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.
|
Comments
Post a Comment