Using function to get previous month

[Susan L]

This seems my week for questions about date functions. (One yesterday,
another today. I need to "get smart" about date functions.)

I have a monthly report completed 5 days after the end of the prvious month.

In it, I have a calculation based on a control that contains the number of
the previous month.

Up until now (January), this expression for the Control Source has worked:
Month(Now())-1. But, of course, "1" for January results in "0", so my scheme
falls apart.

Can someone tell me what date function to use and the syntax for obtaining
the previous month's value that will produce the correct value even in
January?

 

[Jeff Boyce]

Susan

One trick you can use to get the previous month (or the last day of a month,
or ...) is to check out the DateSerial() function.

To get a date (let's say the 1st) of the previous month, you could use
something like:

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

Check Access HELP on DateSerial() for syntax and examples.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Susan L]

Thanks. I have used DateSerial for other purposes. This yields 12/01/2008.
What I need is just the month (12). Do I have to use a Format function to get
that?

I have tried Month(Date)-1 and trimming DateSerial for just the month, with
a 0 for the day. But no luck.

 

[John Spencer (MVP)]

This expression will return the number value of the previous month.

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

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Susan L]

I didn't think of putting Month in front.

Thank you so very much.

 

[Jeff Boyce]

Susan

Month(DateSerial(...)) would return 12 if you used today's date...

Regards

Jeff Boyce
Microsoft Office/Access MVP