ACC: How to Get the Fiscal Year/Month of a Particular Date

• Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

This article demonstrates how you can get the fiscal year or fiscal month of a particular date using an expression or a user-defined function.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

NOTE: Visual Basic for Applications is called Access Basic in Microsoft Access versions 1.x and 2.0. For more information about Access Basic, please refer to the "Introduction to Programming" manual in Microsoft Access version 1.x or the "Building Applications" manual in Microsoft Access version 2.0.

MORE INFORMATION

Using an Expression to Get the Fiscal Year or Fiscal Month

To get the fiscal year or fiscal month of a particular date using an expression, follow these steps:

1. Open the sample database Northwind.mdb (or NWIND.MDB in earlier versions).

2. Create a new query based on the Orders table.

3. In the query's QBE grid, add the following fields.

   NOTE: In the sample expressions below, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating these sample expressions.

         Query: GetFiscalDates
         -------------------------------------------------------------------
         Field: OrderDate
            Table: Orders
            Criteria: Like "*/*/94"
         Field: FYear: Year([OrderDate])-IIf([OrderDate]< _
                  DateSerial(Year([OrderDate]),6,16),1,0)
         Field: FMonth: (Month([OrderDate])+IIf(Day([OrderDate])<16,6,7)-1) _
                        Mod 12+1
   
      NOTE: In Microsoft Access 1.x and 2.0, [Order Date] contains a space.
   
   

4. Run the query, and then scroll through the query datasheet. Note that the fiscal year and fiscal month appear for each record in the Orders table.

   Field: FYear: Year([FieldName])-IIf([FieldName]< _
            DateSerial(Year([FieldName]),9,15),1,0)
   Field: FMonth: (Month([FieldName])+IIf(Day([FieldName])<15,9,10)-1) _
            Mod 12+1

   Field: FYear: Year([FieldName])-IIf([FieldName]< _
            DateSerial(Year([FieldName]),9,15),1,0)+1

Using Sample Functions to Get the Fiscal Year or Fiscal Month

To get the fiscal year or fiscal month of a particular date using sample functions, follow these steps:

1. Create a module and type the following lines in the Declarations section:

         Option Explicit
         Const FMonthStart = 6   ' Numeric value representing the first month
                                 ' of the fiscal year.
         Const FDayStart = 16    ' Numeric value representing the first day of
                                 ' the fiscal year.
         Const FYearOffset = -1  ' 0 means the fiscal year starts in the
                                 ' current calendar year.
                                 ' -1 means the fiscal year starts in the
                                 ' previous calendar year.
   
   

2. Type the following two procedures:

         Function GetFiscalYear (ByVal x As Variant)
            If x < DateSerial(Year(x), FMonthStart, FDayStart) Then
               GetFiscalYear = Year(x) - FYearOffset - 1
            Else
               GetFiscalYear = Year(x) - FYearOffset
            End If
         End Function
   
         Function GetFiscalMonth (ByVal x As Variant)
            Dim m
            m = Month(x) - FMonthStart + 1
            If Day(x) < FDayStart Then m = m - 1
            If m < 1 Then m = m + 12
            GetFiscalMonth = m
         End Function
   
   

3. To test these functions, type each of the following lines in the Debug window (or Immediate window in versions 1.x and 2.0), and then press ENTER.

         ? GetFiscalYear (#7/1/95#)
   

   Note that this line returns the year 1996.

         ? GetFiscalMonth (#8/1/95#)
   

   Note that this line returns the number 2. The 2 represents the second month in the fiscal year.

REFERENCES

For more information about the DateSerial function, search the Help Index for "DateSerial function."

Keywords          : kbprg PgmHowTo ExrStrg
Version           : 1.0 1.1 2.0 7.0 97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbhowto