a DateAdd question?

[Geoff Cox]

Hello

I have found

Between DateAdd("m",-11,Date()) AND DateAdd("m",-12,Date())

which, if I am right, gives me events within the period of 1 month, 12
months ago but would like to find those events between the 1 and the
last day of the month 12 months ago.

So this month being December I want to find events between the 1st and
the 31st of December 2006. Plus a formula which will work for January
2008, February 2008 etc.

Ideas please!

Thanks

Geoff

 

[Douglas J. Steele]

If you were to run that today (4 Dec, 2007), you'd get those events between
4 Dec, 2006 and 4 Jan, 2007.

What you want is:

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

Don't worry: DateSerial will handle everything correctly for all months.

 

[BruceM]

Try DateSerial instead of DateAdd (watch for wrapping in your newsreader;
this is all on one line:
Between DateSerial(Year(Date())-1,Month(Date()),1) And
DateSerial(Year(Date())-1,Month(Date())+1,0)

This will give you from the first to the last day of the current month. The
0 at the end of the expression is the day before the first day of the
specified month. Given today's date, Year is 2007, Month + 1 is January
(December + 1), and 0 is the day before January 1. The advantage of using
the zero is that it works with any month, no matter the number of days.

 

[BruceM]

Douglas used a more concise expression. That would be the one to use.

 

[Geoff Cox]

Try DateSerial instead of DateAdd (watch for wrapping in your newsreader;
this is all on one line:
Between DateSerial(Year(Date())-1,Month(Date()),1) And
DateSerial(Year(Date())-1,Month(Date())+1,0)

This will give you from the first to the last day of the current month. The
0 at the end of the expression is the day before the first day of the
specified month. Given today's date, Year is 2007, Month + 1 is January
(December + 1), and 0 is the day before January 1. The advantage of using
the zero is that it works with any month, no matter the number of days.

My thanks to you both!

Cheers

Geoff