DateSerial; DateAdd

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Chris - 05 Jan 2006 17:39 GMT

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.

Signature

Thanks for your help,
Barb

Reply to this Message

John Spencer - 05 Jan 2006 17:51 GMT

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)

> Can anyone aid me please?
>
[quoted text clipped - 20 lines]
>
> Someone please come to my rescue.

Reply to this Message

Chris - 05 Jan 2006 19:12 GMT

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

Signature

Thanks for your help,
Barb

> Use DateSerial in both cases. Also, instead of adjusting the Year value,
> adjust the Month value.
[quoted text clipped - 31 lines]
> >
> > Someone please come to my rescue.

Reply to this Message