Here is a quick solution on how to calculate the first and last day in a week, month, or year in MS Access. I’ve always ran in to situations when dealing with data on needing to determine how to calculate the first or last day of a time range. In more instances than not, I’m needing to determine the first and last day of a month. In MS Access, you can always find a reason to calculate these dates, so hopefully they will be of some use to you.

The below solutions should work relatively easy by a simple copy & paste of the code. Just in case, I’ve added a working demo of the solution below. Enjoy!

Determine First / Last Day in Time Period in MS Access

Posted: 2013-02-27 | Downloads: 19 | Version: 1.0 | Size: 28.6 kB

Calculate First & Last Day of a Time Range in MS Access

Each of the below functions work within MS Access, and they accept an optional parameter as the reference date. If you choose not to use the optional parameter, the function will use the systems current date as the reference date.

The VBA Functions – MS Access

The VBA Usage – MS Access

Once you have the functions in place within MS Access, you can use them within your VBA code by simply calling the function in one of these three methods below to calculate the first or last date of a time range.
[bullet_list icon="rightarrow"]

  • FirstDayInMonth(#2/25/2013#)
  • FirstDayInMonth(Date)
  • FirstDayInMonth()


The SQL Usage – MS Access

In order to use the function within a Query in MS Access, you can use something similar to the below to calculate the first or last date in a time range.