G
Guest
Is there an EOmonth Add-in for Access similar to the one in excel?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
D
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
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
J
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)
EOMonth = DateSerial(Year(Start_Date),Month(Start_Date)+ 1 + Months,0)
D
Douglas J. Steele
Because I didn't think of it, smart alec! <g>
J
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.
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.
D
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.
one fewer function call, but otherwise is the same.