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 are the first quarter, the next three months belong to the second quarter, and so on.

Usually, when we prepare a Quarterly Report (for a period of three months based on the table above), for a business entity, we use date-range value to filter the required data for the report.

For example: To prepare the Sales Report for the Second Quarter of Sales Year 2017 we will set the date range from April 1st, 2017 to June 30, 2017, as data filtering criteria in a SELECT Query. Probably we may use a Date-Picker control on the parameter entry Form to make it easier to pick and set the date values, rather than typing the date range manually.

If the Report preparation procedure is created by the above-fixed pattern, then the task can be made easier by creating a small Function and using it on the data filtering Query.

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 takes the date value as a parameter. The Month() Function extracts the month number, from the date and uses it as a parameter for the Choose() Function to pick the correct Quarter Number from its list.

When the month value is 1,2 or 3 the GetQrtr() function returns 1 to the calling procedure. The Function can be called from a Query, from other Functions, from Form control, or from a Report Control providing date as a parameter. When the date value passed to the function belongs to April, May, and June will return 2. These months belong to the Second Quarter of the Year. Dates for the next three months return 3 and so on.

Using the Function in Query.

Let us see how we can use this Function in a Sales Query to extract data for Second Quarter 2017 Sales Report. Sample data filtering Query SQL is given below:

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

The GetQrtr() function extracts the Quarter numbers from all the Sales Record Dates, based on the Values we have lined up in the Choose() Function inside the GetQrtr() Function, compares them with the criteria parameter value 2 and filters the records for that period.

You may set up a Parameter Variable within the Query so that it will prompt for the criteria value when the Query Runs and can input the required value directly to filter the data for the report.

When Financial Year is from April to March next Year (Jan - Mar becomes the 4th quarter) still the filter criteria will be 1 to extract the data for the fourth quarter. The report heading Labels will indicate that the report is for the fourth quarter of Financial Year 2017-18.

Earlier Post Link References:

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