Free Lessons Courses Seminars TechHelp Fast Tips Templates Topic Index Forum ABCD

 Home   Courses   Seminars   Templates   TechHelp   Fast Tips   Forums   Help   Contact   Join   Order   Logon More... What's New? Popular Links ------------ Access Courses Access Index Access TechHelp Access Forum ------------ Excel Courses Excel Index ------------ Code Vault Testimonials

# 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

LDOM = DateSerial(Year(NM), Month(NM), "1")

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

 Check out these other pages that may be of interest to you:
 11/25/2022 Application Title 11/24/2022 Full HTML Editor in Access 11/24/2022 Change Database Title 11/23/2022 Other Names 11/21/2022 Check Icon File 11/21/2022 Check Icon File 11/18/2022 Archive Records 11/17/2022 InStr Function 11/16/2022 Numbers As Text 11/15/2022 Merge Tables