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!

[download id=”7″ format=”1″]

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
Public Function FirstDayInMonth(Optional iDate As Variant) As Date
    ' Return the first day in the specified month.
    If IsMissing(iDate) Then
        iDate = Date
    End If
    
    FirstDayInMonth = DateSerial(Year(iDate), Month(iDate), 1)
End Function

Public Function LastDayInMonth(Optional iDate As Variant) As Date
    ' Return the last day in the specified month.
    If IsMissing(iDate) Then
        iDate = Date
    End If
    
    LastDayInMonth = DateSerial(Year(iDate), Month(iDate) + 1, 0)
End Function

Public Function FirstDayInWeek(Optional iDate As Variant) As Date
    ' Returns the first day in the week specified by the
    If IsMissing(iDate) Then
        iDate = Date
    End If
    
    FirstDayInWeek = iDate - Weekday(iDate, vbUseSystemDayOfWeek) + 1
End Function

Public Function LastDayInWeek(Optional iDate As Variant) As Date
    ' Returns the last day in the week specified by the date in iDate.
    If IsMissing(iDate) Then
        iDate = Date
    End If
    
    LastDayInWeek = iDate - Weekday(iDate, vbUseSystemDayOfWeek) + 7
End Function

Public Function FirstDayInYear(Optional iDate As Variant) As Date
    ' Return the first day in the specified year.
    If IsMissing(iDate) Then
        iDate = Date
    End If
    
    FirstDayInYear = DateSerial(Year(iDate), 1, 1)
End Function

Public Function LastDayInYear(Optional iDate As Variant) As Date
    ' Return the last day in the specified year.
    If IsMissing(iDate) Then
        iDate = Date
    End If
    
    LastDayInYear = DateSerial(Year(iDate), 12, 31)
End Function
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”]

  • {vb}FirstDayInMonth(#2/25/2013#){/vb}
  • {vb}FirstDayInMonth(Date){/vb}
  • {vb}FirstDayInMonth(){/vb}

[/bullet_list]

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.

SELECT iField.iTable, 
  FirstDayInMonth(Date()) AS FirstDayInMonthDynamic, 
  FirstDayInMonth(#2/25/2013#) AS FirstDayInMonthNamed,
  FirstDayInMonth([MyDate]) AS FirstDayInMonthParameter
FROM iTable;