Month problem

[JayDe]

I have some code where I calculate this month minus 2 month. This goes fine
until I get to august. When I use DateSerial to deduct 2 month I get to July,
and this makes no sence. I made the following testcode.

Sub StrangeMonth()

Dim MyDate As Date

MyDate = #8/31/2010#
MsgBox DatePart("m", DateSerial(Year(MyDate), _
Month(MyDate) - 1, Day(MyDate))) ' MsgBox = 7
MsgBox DatePart("m", DateSerial(Year(MyDate), _
Month(MyDate) - 2, Day(MyDate))) ' MsgBox = 7
MsgBox DatePart("m", DateSerial(Year(MyDate), _
Month(MyDate) - 3, Day(MyDate))) ' MsgBox = 5

End Sub

Can anyone test if they get the same result. I work on a Norwegian version
of Access 2007

If the same result turns upp in other computers. Is there a workaround?

Regards
JayDe

 

[Douglas J. Steele]

The problem is that June doesn't have 31 days, so that
DateSerial(Year(MyDate), Month(MyDate) - 2, Day(MyDate)) resolves to
7/1/2010

 

[John Spencer]

So instead of using DateSerial, you might be better off using DateAdd.
DateAdd("m",-2,[MyDate])


OR if you are attempting to get the last day of the month no matter what day
of the month the source date has then use something like the following:

Given #8/31/2010# as the source date (Or ANY date in August of 2010) you would
see results like the following

08/31/2010 for DateSerial(Year(MyDate), Month(MyDate)+1, 0)
07/31/2010 for DateSerial(Year(MyDate), Month(MyDate), 0)
06/30/2010 for DateSerial(Year(MyDate), Month(MyDate)-1, 0)
05/31/2010 for DateSerial(Year(MyDate), Month(MyDate)-2, 0)
04/30/2010 for DateSerial(Year(MyDate), Month(MyDate)-3, 0)

With DateAdd you are guaranteed to get the right month and the corresponding
date EXCEPT for the last day (or days) of the month.

Sept 30 to August you will end up with August 30
Feb 28 to Jan 28
Mar 31 to Feb 28 (or 29 in leap years)

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