Show Quarters Between Two Dates…

Interesting question in a Forum whereby the Poster wanted a list of the Quarters between two dates.  While this request doesn’t come up often when it does there’s an answer…

While you *think* you could run this in one query it’s just not possible because in order for the query to provide the answer it needs data as in dates to process.  Other than that you are just going to get the beginning Quarter and the ending Quarter.  The query cannot *think* those dates into existence.  So, we have to *trick* it into seeing those dates.  (You could use this same procedure for projecting months or years, as well.)

Step 1
In your database create a table named tblCount and add one field named dNumber – Long Integer and make it a Primary Key. Start with the number 0(zero) and continue adding until you get to 1724.  (Okay, tedious but worth it so just keep lugging away till you get to that number.)

Side note:  A way faster way to get those values in a table is inside a transaction with a QueryDef (posted by MarkK in Access World Forums)  Just copy\paste the below code in a new Module and then put…

?fInsertNumbers()

…in the Immediate window and press enter.

Option Compare Database
Option Explicit
Const SQL As String = "INSERT INTO tblCount(dNumber) VALUES( p0 )"
Function fInsertNumbers()
'From https://www.access-programmers.co.uk/forums/showthread.php?t=300693
    
    Dim i As Integer
    
    DBEngine.BeginTrans
    With CurrentDb.CreateQueryDef("", SQL)
        For i = 1 To 1724
            .Parameters(0) = i
            .Execute
        Next
        .Close
    End With
    DBEngine.CommitTrans

End Function

Tip!  If you download the sample file below tblCount is included and already populated and Module is included for future use.

Step 2
Create a SELECT query (mine is named qryGetProjectDays) based off of the Table that has the Project Start Date and End Date and drop tblCount in and do NOT create any Joins. You need one field…

TheDays: DateAdd("d",[dNumber],[pStartDate])

and add <=[pEndDate] to the Criteria section of that one field (example of what you should have below)…

SELECT DateAdd("d",[dNumber],[pStartDate]) AS TheDays
FROM tblYourTable, tblCount
WHERE (((DateAdd("d",[dNumber],[pStartDate]))<=[pEndDate]))
ORDER BY DateAdd("d",[dNumber],[pStartDate]);

Note: When you preview the query provided you have entered a Start Date and and End Date you will see a record for every day in the Project.

Step 3
Create a new SELECT query and place qryGetProjectDays (or whatever you named your query) in your new SELECT query. Place…

CDate(Format([TheDays],"q-yyyy"))

…on the Field line below (example below).  You should now see all the Quarters between two dates entered.

SELECT CDate(Format([TheDays],"q-yyyy")) AS ProjectQuarters
FROM qryGetProjectDays
GROUP BY CDate(Format([TheDays],"q-yyyy"));

All done!  You can download the sample database here.

 884 total views,  1 views today