DateAdd problem

  • Thread starter Thread starter Guest
  • Start date Start date
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
 
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)
 
=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
 
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
Click to expand...
Click to expand...
 
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top