Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Activity Dates and Quarterly Reports

There are four Quarters in a Year:

Jan - Mar = 1st Quarter
Apr - Jun = 2nd
Jul - Sep = 3rd
Oct - Dec = 4th

First three months of the year is first quarter, next three months belongs to second Quarter and so on.

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

For example: To prepare 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 on the above fixed pattern then the task can be made easier by creating a small Function and use it on the data filtering Query

GetQrtr() Function Code is given below:


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 parameter. The Month() Function extracts the month number from date and uses it as parameter for the Choose() Function to pick the correct Quarter Number from it's 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 by passing date as parameter. When date value passed to the function belongs to April, May, June will returns 2. These months belongs to the Second Quarter of the Year. Dates from next three months returns 3 and so on.


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


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


The GetQrtr() function extracts the Quarter number 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 setup 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 report.


When Financial Year is from April to March next Year (Jan - Mar become 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.

Share:

3 comments:

  1. "still the filter criteria will be 1"
    I thought the filtering criteria was a date range, e.g. from April 1st, 2017 to June 30, 2017

    ReplyDelete
  2. Data filtering is done based on Month Values.

    ReplyDelete
  3. Financial Year-Ending can be any date, not necessarily on March 31st. This will be the logical end period for tax reporting purposes or end of busiest business activities. But, the transaction dates entered into the computer system is based on actual dates in a Calendar Year.

    If the fourth Quarter of a Financial Year is January 1st to March 31st next Year then we must pick the trasactions of Jan-Mar or 1st Quarter of the next Calender Year.

    Hence, the criteria value will be 1 for filtering data from the first Quarter of the Calendar Year and to report it as fourth Quarter performance of the Financial Year.

    ReplyDelete

Translate



PageRank
Your email address:

Delivered by FeedBurner

Search

Infolinks Text Ads

blog.feedspot.com Microsoft Access Blogs

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Labels

Forms How Tos Functions MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Queries External Links msaccess reports msaccess tips Accesstips Menus and Toolbars MsaccessLinks Process Controls Art Work Downloads msaccess How Tos Graph Charts msaccessQuery List Boxes Array Command Buttons Emails and Alerts Query Combo Boxes Custom Wizards DOS Commands Data ms-access functions msaccess functions msaccess graphs msaccess reporttricks msaccessprocess security advanced Access Security Calculation Custom Functions Data Type Macros Menus Property Report Top Values VBA Variables msaccess email msaccess menus progressmeter Access2007 Auto-Number Command Button Copy Field Type Fields Form Join Microsoft Numbering System Records Security Split SubForm Table Utility Workgroup database msaccess wizards Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Conditional Formatting Controls Data Filtering Defining Pages Diagram Disk Dynamic Lookup Error Handler Excel Export Expression External Filter Form Instances Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Methods Monitoring Object Reference Objects Octal Numbers Operating System Paste Primary-Key Product Rank Reading Recordset Rich Text Sequence SetFocus Summary Tab-Page Tables Time Difference Union Query User Users Water-Mark Word automatically commands function hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar tutorial updating upload vba code

Featured Post

MS-Access Class Module and VBA

Last few weeks we have learned how to use User Defined Type (UDT)  by creating a complex data structure and we know UDT’s strength or weakne...

Labels

Blog Archive

Recent Posts