User defined function to return first and last day of previous month

I have a recurring need from database to database to return the first day of last month as well as the last day of last month.  Usually in response to running a batch of update queries or possibly a data range for a  report.

So here’s the technique I’ve implemented:

On the form for running the update queries or the form for running the reports, I add two text box controls and set the format to ‘mm/dd/yy’ and the default value to:

txtBeginMonth, default value: =fncReturnFirstDayLastMonth()

txtEndMonth, default value: =fncReturnLastDayLastMonth()

 

In a new module for the database (not for the particular form), I then create the following two functions:

Function fncReturnFirstDayLastMonth() As Date

    Dim val As Variant
    
    Dim strLastMonthDay As String
   Dim strLastMonthMonth As String
    Dim strLastMonthYear As String
    
    strLastMonthDay = "01"
    strLastMonthYear = Format(Date, "yyyy")
    
    Select Case Format(Date, "mmmm")
    
        Case Is = "January"
            strLastMonthMonth = "12"
            strLastMonthYear = Format(Date, "yyyy") - 1
        Case Is = "February"
            strLastMonthMonth = "01"
        Case Is = "March"
            strLastMonthMonth = "02"
        Case Is = "April"
            strLastMonthMonth = "03"
        Case Is = "May"
            strLastMonthMonth = "04"
        Case Is = "June"
            strLastMonthMonth = "05"
        Case Is = "July"
            strLastMonthMonth = "06"
        Case Is = "August"
            strLastMonthMonth = "07"
        Case Is = "September"
            strLastMonthMonth = "08"
        Case Is = "October"
            strLastMonthMonth = "09"
        Case Is = "November"
            strLastMonthMonth = "10"
        Case Is = "December"
            strLastMonthMonth = "11"
    End Select
    
   val = CDate(strLastMonthMonth & "/" & strLastMonthDay & "/" & strLastMonthYear)
    
    
    
    fncReturnFirstDayLastMonth = val
End Function

Function fncReturnLastDayLastMonth() As Date

    Dim val As Variant
    
    Dim strLastMonthDay As String
    Dim strLastMonthMonth As String
    Dim strLastMonthYear As String
    
    strLastMonthDay = "01"
    strLastMonthYear = Format(Date, "yyyy")
    
    Select Case Format(Date, "mmmm")
    
        Case Is = "January"
            strLastMonthDay = "31"
            strLastMonthMonth = "12"
            strLastMonthYear = Format(Date, "yyyy") - 1
        Case Is = "February"
            strLastMonthDay = "31"
            strLastMonthMonth = "01"
        Case Is = "March"
            strLastMonthDay = "28"
            strLastMonthMonth = "02"
        Case Is = "April"
            strLastMonthDay = "31"
            strLastMonthMonth = "03"
        Case Is = "May"
            strLastMonthDay = "30"
           strLastMonthMonth = "04"
        Case Is = "June"
            strLastMonthDay = "31"
            strLastMonthMonth = "05"
        Case Is = "July"
            strLastMonthDay = "30"
            strLastMonthMonth = "06"
        Case Is = "August"
            strLastMonthDay = "31"
            strLastMonthMonth = "07"
        Case Is = "September"
            strLastMonthDay = "31"
            strLastMonthMonth = "08"
        Case Is = "October"
            strLastMonthDay = "30"
            strLastMonthMonth = "09"
        Case Is = "November"
            strLastMonthDay = "31"
            strLastMonthMonth = "10"
        Case Is = "December"
            strLastMonthDay = "30"
            strLastMonthMonth = "11"

    End Select
    
   val = CDate(strLastMonthMonth & "/" & strLastMonthDay & "/" & strLastMonthYear)
    
    fncReturnLastDayLastMonth = val
End Function

Now, when the user enters the form, the first and last day of last month are presented to the user.  The user may feel free to change those dates prior to running update queries or reports.  But if he/she so chooses, these dates are already prepared and ready to go.

 

 

This entry was posted in Microsoft Access Solutions and tagged , , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *