This article shows you how to get the fiscal year or fiscal month
of a particular date by 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.
Back to the top
Using an Expression to Get the Fiscal Year or Fiscal Month
To get the fiscal year or fiscal month of a particular date by using an
expression, follow these steps:
| 1. | Open the sample database Northwind.mdb (or NWIND.MDB in version 2.0
or earlier).
|
| 2. | Create the following new query based on the Orders table.
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, there is a space in the Order
Date field name.
|
| 3. | 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.
|
NOTE: You can use the FYear and FMonth expressions in your database by
modifying the Date field, the day and the month, to the appropriate
values for your fiscal year. For example, if the fiscal year begins on 9/15
of the current calendar year, you can modify the previous expressions as
follows:
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
If the fiscal year begins on 9/15 of the previous calendar year, you can
modify the FYear expression as follows:
Field: FYear: Year([FieldName])-IIf([FieldName]< _
DateSerial(Year([FieldName]),9,15),1,0)+1
Back to the top
Using Sample Functions to Get the Fiscal Year or Fiscal Month
To get the fiscal year or fiscal month of a particular date by 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 after each one:
? 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. |
Back to the top
For more information about the DateSerial function, search the Help Index
for "DateSerial function."
Back to the top
