Find Last Day of Month
How to calculate the last day of any given month with VBA
Q: How do I calculate the LAST day of the current month?
A: Figuring out the first and
last day of the month isn't that hard, but it's hard enough to where
you'd want to make a custom function for it, instead of just trying to
slip it into a query.
The first step is to create a PUBLIC FUNCTION for it in a module. So
create a new Module:
Here's the code I wrote. Take a look at it, and I'll explain it in
detail momentarily:
Public Function
LastDayOfMonth(D As Date) As Date
Dim LDOM As Date
Dim NM As Date
NM = DateAdd("m", 1, D)
LDOM = DateSerial(Year(NM), Month(NM), "1")
LDOM = DateAdd("d", -1, LDOM)
LastDayOfMonth = LDOM
End Function
First, I created two date variables - one a temporary holder for the
Last Day of the Month (LDOM) and another to figure out what NEXT month's
date is. Because the number of the last day of the month can change (28,
29, 30, 31), I decided it would be easier to find the FIRST day of NEXT
month and then subtract one day.
So, I said Next Month (NM) is equal to the date in question PLUS one
month. For
more help on DateAdd see this tutorial.
Next, the LDOM is equal to the first day of NM's year and month. The
DateSerial function just assembles a date from it's parts: Year, Month,
Day. In this case, send a "1" to get the first day of the month.
Finally, I used DateAdd to subtract one day from LDOM which gives me the
LAST day of the current month (or whatever month you send to the
function.)
Now, where do you USE this? Well, you can use it in a query as a
calculated field. Just set up a table with a bunch of dates:
Put some dates in...
Create a query with that date field, and then insert your function as a
calculated query field.
Now run the query to check your results:
You can also use this function in your reports, forms, or even an AfterUpdate
event to set a value in a field.
By Richard Rost
Click here to sign up for more FREE tips
| 
