DateSerial; DateAdd

[Guest]

Can anyone aid me please?

I want to retrieve (query) items that have a date that falls within the last
12 months ending the end of last month. For example... we are in Jan 06, I
want to retrieve between 1/1/05 and 12/31/05 -- only and inclusively. My
query must retrieve the previous 12 months ending with the end of the prior
month.

I am using now:

Between DateSerial(Year(Date())-1,Month(Date()),1) And DateAdd("m",0,Date())

It returns my data ok (1/1/05 - 12/31/05) ...but includes the current month
1/1/06. Thus its giving me 13 months -- Jan 06 being month-to-date data. I
only want (and have room for on the report!) 12 full months, and cannot use
the month-to-date data I get.

Someone please come to my rescue.

 

[John Spencer]

Use DateSerial in both cases. Also, instead of adjusting the Year value,
adjust the Month value.

Between DateSerial(Year(Date()), Month(Date())-12,1) and
DateSerial(Year(Date()),Month(Date()),0)

The Zero day of the month is the Last day of the previous month. Think of
it as subtracting 1 from the 1st day of the month

DateSerial(2006,1,1) -1 which can be done as DateSerial(2006,1,1-1)

 

[Guest]

That works great, John. Thanks so much for the help.


--
Thanks for your help,
Barb

Use DateSerial in both cases. Also, instead of adjusting the Year value,
adjust the Month value.

Between DateSerial(Year(Date()), Month(Date())-12,1) and
DateSerial(Year(Date()),Month(Date()),0)

The Zero day of the month is the Last day of the previous month. Think of
it as subtracting 1 from the 1st day of the month

DateSerial(2006,1,1) -1 which can be done as DateSerial(2006,1,1-1)