Date function spanning across time periods.

[Robin Merrill]

Hi.

I need the syntax in Access 2007 that will subtract one month from the current date and always get the correct prior month, i.e. in January 2012, month(date())-1 would pull December 2011 dates and in July 2012 it would pull June 2012.

Thanks for your help.
Robin

[Scott W]

This will give the date one month ago:

DateAdd("m",-1,Date())

"m" tells it to count by months, -1 is the interval, and the Date() is just the date function, but it can be any Date variable.

[Rabbit]

Use the DateAdd function to add a negative month.

DateAdd("m", -1, someDateField)

[sierra7]

DateAdd("m", -1, Date)

S7

[NeoPa]

Three good answers. I can also confirm that this handles difficult months correctly too.

For instance, using a value for 31st March returns the last day of February (as illustrated below) :

?DateAdd("m",-1,#31/3/2011#)
28/02/2011

If I may be allowed to make a suggestion, Post #2, being the first posted and answering pretty fully as well, might be a good selection for Best Answer (With no disrespect to either of the other posts which were equally correct).

[Robin Merrill]

Thanks for all of your quick responses. I belive I need to use this funcgtion instead of the simple month(date()) -1 in my reports that will run every month for multiple years, is that right?

Thanks again.
Robin

[NeoPa]

Month(Date()) - 1

This won't work very well for January.

Only you know exactly what you need. Certainly what you say sounds sensible but we're not in a good position to cofirm as we don't know what your requirements are.