This Access tutorial explains how to find first date of month, last date of month using Access Expression and VBA Function.
You may also want to read:
Excel first date of month and last date of month
Access first date of month
To find first date of month in Access, first we have to understand DateSerial, you can use it to combine the year, month and day to a date.
Syntax of Date Function
DateSerial( year, month, day )
Example
| Formula | Result | Explanation |
| DateSerial(2012,1,1) | 1/1/2012 | Combine all three arguments to date 2012 Jan 1st |
| DateSerial(YEAR(Date()),MONTH(Date()),DAY(Date())) | 13/3/2016 | Year Function returns year of the date, Month function returns month of a date, Day function returns day of a date.
Date() Function is to return today’s date. Assume today is March 13th, use today’s year, month, day as the arguments to return today’s date |
To get the first date of specific month, say this month, use today’s month and year, and then use 1 for day argument
=DateSerial(YEAR(Date()),MONTH(Date()),1)
Access last date of month
There are several ways to find the last date of month, I am introducing two ways that I like most.
Unlike first date of month, we cannot directly input “31” in the day argument because every month has different number of days. Instead we can find the first day of next month and then minus 1 day. Access is very clever that when you add 1 month to December, the year also adds 1, so this trick also works for year end. The formula below returns the last date of current month.
DateSerial(Year(Date()),Month(Date())+1,1)-1
We can also simply this formula using 0 in the day argument instead of using -1. 0 returns the last date of previous month.
DateSerial(Year(Date()), Month(Date()) + 1, 0)
Access VBA first date and last date of month
In VBA, the relevant Function names are same as the Expression Function names, except that Date() Function in Expression becomes Date (without brackets).
Example
| Formula | Result | Explanation |
| Dateserial(2012,1,1) | 1/1/2012 | Combine all three arguments to date 2012 Jan 1st |
| DateSerial(Year(Date), Month(Date), Day(Date)) | 13/3/2016 | Assume today is March 13th, use today’s year, month, day as the arguments to return today’s date. |
| DateSerial(Year(Date), Month(Date), 1) | 1/3/2016 | First date of current month |
| DateSerial(Year(Date), Month(Date)+1, 0) | 31/3/2016 | Last date of current month |
