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

 

[Linq Adams via AccessMonster.com]

Adding or subtracting from dates is best done with the DateAdd() function. To
subtract you simply use a negative number in the second argument. In your
example, to subtract 2 months from MyDate

DateAdd("m", -2, MyDate)

 

[tom_willpa]

high quality Soccer jerseys NBA Jersey tracksuit and jackets, GHD
hairstraightener supplier from www.willpa.com

Are you a Retail businessman who bother by the purchase price? China
Cheapest TOP wholesale website can help you

we are specialize in replica sport goods manufacturing in china, we can
offer you all kinds of soccer jersey, NBA jersey,shoes and so on. they are
the best brand replica goods whih are look the same as the original goods.
excellent quality and steady supply for them. we have been marketed in Europe
and American for 3 year. all the goods we offer are AAA quality. our soccer
jersey are Thailand style. If any goods you buy from my company have problem,
we will refund or resend them again. Most of ourProducts have no minimum
order requirements,soyou can shop retail goods at wholesale prices. if you
can buy more than 300usd. We offer free shipping. The more you buy the more
discount for you.

National soccer jerseys: http://www.willpa.com
Club soccer jerseys: http://www.willpa.com
NBA Jerseys: http://www.willpa.com
T-shirt and shirt: http://www.willpa.com
Tracksuit: http://www.willpa.com
Hoody & Jackets: http://www.willpa.com
UGG boots: http://www.willpa.com
Hair style: http://www.willpa.com
shopping Index: http://www.willpa.com

EMS shipping. 7days arrive, paypal accept

want more information pls contact us or check our website: www.willpa.com