G

Guest

In the following expression, Access is not giving me the month-end date:

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

A

Allen Browne

To get the last day of the month 3 months back, add 1 day, subtract 3 monhs,
and subtract 1 day:

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

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??

Rajni

Klatuu said:

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:

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

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??

Rajni

Klatuu said:

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:

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