Returning a dynamic date range

[Ali]

In a query I would like to be able to return all records that appear within
a date range.

The date range will be worked out from todays date and will always be the
proceeding 12 mths but not including the current month.

Eg Today's Date = 2nd Feb 05
The query would return records in the date range 1st Feb 04 to 31st Jan 05

Or Today's Date = 5th Jan 05
The query would return records in the date range 1st Jan 04 to 31st Dec 04

How can I do this? I assume a combination of DateAdd and DateSerial but
can't work out how it will work over the year end period.

 

[Gary Walter]

In a query I would like to be able to return all records that appear within
a date range.

The date range will be worked out from todays date and will always be the
proceeding 12 mths but not including the current month.

Eg Today's Date = 2nd Feb 05
The query would return records in the date range 1st Feb 04 to 31st Jan 05

Or Today's Date = 5th Jan 05
The query would return records in the date range 1st Jan 04 to 31st Dec 04

How can I do this? I assume a combination of DateAdd and DateSerial but
can't work out how it will work over the year end period.

Hi Ali,

FirstDayOfMonthLastYear: DateSerial(Year(Date())-1, Month(Date()) , 1)
LastDayOfPreviousMonth : DateSerial(Year(Date()), Month(Date()) , 0)

?Date()
2/21/2005
?DateSerial(Year(Date())-1, Month(Date()) , 1)
2/1/2004
?DateSerial(Year(Date()), Month(Date()), 0)
1/31/2005

The "time value" of these expressions
will 12:00 midnight.

If your [DateField] does not contain time values:

WHERE [DateField] BETWEEN
DateSerial(Year(Date())-1, Month(Date()) , 1)
AND
DateSerial(Year(Date()), Month(Date()) , 0)

If it does contain time values:

WHERE [DateField] >=
DateSerial(Year(Date())-1, Month(Date()) , 1)
AND
[DateField] <
DateSerial(Year(Date()), Month(Date()), 1)

The "0" is a little tricky and may be easier to
understand if you think of it as "1-1"

so you might think of

DateSerial(Year(Date()), Month(Date()) , 0)

as

give me the first day of this month

DateSerial(Year(Date()), Month(Date()) , 1)

then subtract one day to get last day of the
previous month

DateSerial(Year(Date()), Month(Date()), 1-1)



Gary Walter