DateAdd problem


G

Guest

In the following expression, Access is not giving me the month-end date:
=DateAdd("m",-3,[text0])

For example, if [text0]= #3/31/05# the result is 12/31/04, BUT if
[text0]=#6/30/04# the result is 3/30/04 (I thought Access would automatically
return 3/31/04). What the heck!
As always, thanks for the help!
Russ
 
Ad

Advertisements

A

Allen Browne

To get the last day of the month 3 months back, add 1 day, subtract 3 monhs,
and subtract 1 day:
=DateAdd("m",-3,[text0]+1)-1

That should work if the control contains the last day of the month. If you
want the last day of the month 3 months ago regardless of which day of the
month is in the text box, try:
=DateSerial(Year([text0]), Month([text0])-2, 0)
 
G

Guest

=MonthEndDate(DateAdd("m",-3,[text0]))

Function MonthEndDate(dtmBaseDate As Date) As Date
Dim intCurrMonth As Integer

intCurrMonth = DatePart("m", dtmBaseDate)
While DatePart("m", dtmBaseDate) = intCurrMonth
dtmBaseDate = DateAdd("d", 1, dtmBaseDate)
Wend
MonthEndDate = DateAdd("d", -1, dtmBaseDate)
End Function
 
G

Guest

I have also the same probelm as Russ G have?
Is this a standard Microsoft Bug??

No any other solution??

Thanks in advance.
Rajni

Klatuu said:
=MonthEndDate(DateAdd("m",-3,[text0]))

Function MonthEndDate(dtmBaseDate As Date) As Date
Dim intCurrMonth As Integer

intCurrMonth = DatePart("m", dtmBaseDate)
While DatePart("m", dtmBaseDate) = intCurrMonth
dtmBaseDate = DateAdd("d", 1, dtmBaseDate)
Wend
MonthEndDate = DateAdd("d", -1, dtmBaseDate)
End Function


RussG said:
In the following expression, Access is not giving me the month-end date:
=DateAdd("m",-3,[text0])

For example, if [text0]= #3/31/05# the result is 12/31/04, BUT if
[text0]=#6/30/04# the result is 3/30/04 (I thought Access would automatically
return 3/31/04). What the heck!
As always, thanks for the help!
Russ
 
Ad

Advertisements

A

Allen Browne

Surely 3 months before June 15 is March 15.
Why do you consider this a bug?

If you want the last day of the month 3 months before the date in text box
Text0, try:
=DateSerial(Year([text0]), Month([text0])-2, 0)
as explained.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Rajnikant said:
I have also the same probelm as Russ G have?
Is this a standard Microsoft Bug??

No any other solution??

Thanks in advance.
Rajni

Klatuu said:
=MonthEndDate(DateAdd("m",-3,[text0]))

Function MonthEndDate(dtmBaseDate As Date) As Date
Dim intCurrMonth As Integer

intCurrMonth = DatePart("m", dtmBaseDate)
While DatePart("m", dtmBaseDate) = intCurrMonth
dtmBaseDate = DateAdd("d", 1, dtmBaseDate)
Wend
MonthEndDate = DateAdd("d", -1, dtmBaseDate)
End Function


RussG said:
In the following expression, Access is not giving me the month-end
date:
=DateAdd("m",-3,[text0])

For example, if [text0]= #3/31/05# the result is 12/31/04, BUT if
[text0]=#6/30/04# the result is 3/30/04 (I thought Access would
automatically
return 3/31/04). What the heck!
As always, thanks for the help!
Russ
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top