EOMonth Add-in

[Guest]

Is there an EOmonth Add-in for Access similar to the one in excel?

 

[Douglas J. Steele]

You can add or subtract months using DateAdd. Once you've done that, you can
get the end of the month using DateSerial.

It's messy, but the following should work:

Function EOMonth(Start_Date As Date, Months As Integer) As Date

Dim dtmWorking As Date

dtmWorking = DateAdd("m", Months, Start_Date)
EOMonth = DateSerial(Year(dtmWorking), Month(dtmWorking) + 1, 0)

End Function

 

[John Spencer (MVP)]

Why the extra step? Is it faster than the following or just easier to understand?

EOMonth = DateSerial(Year(Start_Date),Month(Start_Date)+ 1 + Months,0)

 

[Douglas J. Steele]

Because I didn't think of it, smart alec! <g>

 

[John Spencer (MVP)]

I wasn't being a smart alec. I sincerely wanted to know.

I've seen code that looks like it should run faster (two lines) than code of 50
lines. When I tested, I found that the 50 lines of code returned the same
results, but it was 8 to 10 faster. The actual difference for one iteration was
something on the order of 200 milliseconds, but in a loop through a lot of
records it made a difference.

 

[Douglas J. Steele]

In this case, I have to believe the 1 liner will be faster since it involves
one fewer function call, but otherwise is the same.