Looping by Month: Lookup Table

This is the third in a series of articles about looping code one month at a time.

Writing a loop that iterates one month at a time is a subtly tricky problem. The difficulty isn't so much in writing the code itself.  The challenge is to write the code in such a way that your intent is clear to the reader.

Using a monthly lookup table to loop by month

A monthly calendar table has many uses.  Most of those uses involve simplifying queries that involve grouping records by month.  However, if you already have access to such a query, you can repurpose it to loop one month at a time.

Sub ForMonthsLookupTable(StartDate As Date, EndDate As Date)
    Dim s As String
    s = " SELECT MoStart, DaysInMo" & _
        " FROM YrMo" & _
        " WHERE MoEnd >= " & Dt(StartDate) & _
        "   AND MoStart <= " & Dt(EndDate) & _
        " ORDER BY MoStart"
        
    With CurrentDb.OpenRecordset(s, dbOpenForwardOnly)
        Do Until .EOF
            Debug.Print Format(!MoStart, "mmmm yyyy"); ":"; !DaysInMo; "days"
            .MoveNext
        Loop
    End With
End Sub

Let's run the sample code above with a couple of dates:

ForMonthsLookupTable #11/4/2020#, #2/17/2021#
November 2020: 30 days
December 2020: 31 days
January 2021: 31 days
February 2021: 28 days

But why?

OK, it's nice to know that this is possible, but when would you ever actually use this approach instead of one of the others?  After all, it requires quite a few extra lines of code just to write a simple loop.

I can think of two examples:

(1) You want to use a custom field from your YrMo table

I demonstrated this above with the rather contrived "DaysInMo" field.  But let's say your YrMo table actually contains some built-in business logic.  For example, imagine that one of the fields was HolidayCount.  That's the sort of information that may be unreliable to calculate, especially if your company's holiday policy changes from one year to the next.  If you need to make use of one of the fields in the YrMo table, you might as well use the YrMo table itself to control your loop.

(2) You need to reference a field from another table

Let's say you're implementing some sort of custom invoice aging process.  Joining the YrMo table to your invoice table could be a handy way to get access to the information you need for your custom calculations.  Once again, if you will be accessing the results of a recordset that involves the YrMo table, you might as well use that recordset for loop control.

Image by kropekk_pl from Pixabay