Activity Dates and Quarterly Reports

Introduction.

There are four Quarters in a Year:

Jan - Mar = 1st Quarter

Apr - Jun = 2nd

Jul - Sep  = 3rd

Oct - Dec = 4th

The first three months of the year make up the first quarter, the next three months the second quarter, and so on.

When preparing a quarterly report (covering three months as defined above) for a business entity, we usually apply a date range filter to extract the required data.

For example, to prepare the Sales Report for the second quarter of 2017, we would set the date range from April 1, 2017, to June 30, 2017 as the filtering criteria in a SELECT query. To make this step easier, instead of manually typing the date range, we might use a date-picker control on a parameter entry Form to select the dates conveniently.

If the report preparation process follows a fixed quarterly pattern, the task can be further simplified by creating a small function and using it directly in the query’s filtering criteria.

GetQrtr() Function Code.

Public Function GetQrtr(ByVal mPeriod As Date) As Integer

Dim mMonth As Integer

On Error GoTo GetQrtr_Err

mMonth = Month(Nz(mPeriod, 0))

If mMonth > 0 Then
    GetQrtr = Choose(mMonth, 1, 1, 1, 2, 2, 2, 3, 3, 3, 4, 4, 4)
else
    GetQrtr = 0
End If

GetQrtr_Exit:
Exit Function

GetQrtr_Err:
GetQrtr = 0
Resume GetQrtr_Exit
End Function

The GetQrtr() function accepts a date value as its parameter. The Month() function extracts the month number from the given date, which is then used as an argument for the Choose() function to return the correct quarter number.

For example, if the month is January, February, or March, the GetQrtr() function returns 1 to the calling procedure. If the month falls in April, May, or June, the function returns 2, representing the second quarter. Similarly, dates in July through September return 3, and dates in October through December return 4.

This function can be called from a query, another function, a form control, or a report control, simply by providing a date as the parameter.

Using the Function in Query.

Let us now see how the GetQrtr() Function can be used in a sales query to extract data for the Second Quarter 2017 Sales Report. A sample SQL statement is shown below:

SELECT SALESTABLE.*
FROM SALESTABLE
WHERE SALESTABLE.SALESMANCODE = "ABC" 
  AND GetQrtr([SALESDATE]) = 2;

Here, the GetQrtr() function extracts the quarter number from each sales record’s date, based on the values defined in the Choose() function within GetQrtr(). The results are then compared against the criteria value, which filters the records for the second quarter.

Alternatively, you may set up a parameter variable within the query so that, when the query runs, it prompts for the criteria value. This allows you to directly enter the desired quarter number and filter the data dynamically for any report.

In cases where the financial year runs from April to March, the quarter numbering shifts. For example, January–March would be considered the fourth quarter of the financial year. In such a case, the filter criteria would still be 1 (for the first calendar quarter). But the report’s heading labels should indicate that it represents the fourth quarter of the financial year 2017–18.

Earlier Post Link References:

• Custom Calculator and Eval Function
• Calculating Workdays From Date-Range
• Useful Report Function
• Calculating Time Difference