Fiscal Year in Access

  • Thread starter Thread starter bgallagher
  • Start date Start date
B

bgallagher

I am trying to set up a data access page with a pivot
table. Drill down includes Year, Quarter, Month, and
Day. The table is based on an Access Query. Is there an
easy way to change from calendar year display to fiscal
year display. Fiscal year starts July 1. Any help will
be appreciated, even if I can't change to FY.
Thanks
 
bgallagher said:
I am trying to set up a data access page with a pivot
table. Drill down includes Year, Quarter, Month, and
Day. The table is based on an Access Query. Is there an
easy way to change from calendar year display to fiscal
year display. Fiscal year starts July 1. Any help will
be appreciated, even if I can't change to FY.
Thanks
Click to expand...

If it helps, here are a couple of functions I use to extract the fiscal
year and fiscal month for a given date. These functions extract the FY
start month from a Profile table, but you could easily just hard-code
the value 7 (July) instead.

'----- start of function code -----
Function fncFiscalYear(InputDate As Variant) As Variant
' Given a date, returns the fiscal year it belongs in, based on the
' fiscal-year start month defined in the Profile.
'
' Copyright © 2002, Dirk Goldgar
' License is granted to use this code in your applications,
' provided the copyright notice remains intact.

Static intFYStart As Integer

' If we haven't already done so, get the start month of the fiscal
year
' from the Profile.
If intFYStart = 0 Then
intFYStart = fncProfileItem("FYStartMonth", 1)
End If

' Now use that information to calculate the fiscal year.
If IsNull(InputDate) Then
fncFiscalYear = Null
ElseIf IsDate(InputDate) Then
fncFiscalYear = Year(InputDate)
If intFYStart > 1 Then
If Month(InputDate) >= intFYStart Then
fncFiscalYear = Year(InputDate) + 1
End If
End If
Else
Err.Raise 5 'Invalid argument
End If

End Function

Function fncFiscalMonth(InputDate As Variant) As Variant
' Given a date, returns the month of the fiscal year in which it
falls,
' based on the fiscal-year start month defined in the Profile.

'
' Copyright © 2002, Dirk Goldgar
' License is granted to use this code in your applications,
' provided the copyright notice remains intact.

Static intFYStart As Integer

Dim intMonth As Integer

' If we haven't already done so, get the start month of the fiscal
year
' from the Profile.
If intFYStart = 0 Then
intFYStart = fncProfileItem("FYStartMonth", 1)
End If

' Now use that information to calculate the fiscal year.
If IsNull(InputDate) Then
fncFiscalMonth = Null
ElseIf IsDate(InputDate) Then
intMonth = (Month(InputDate) - intFYStart) + 1
If intMonth < 1 Then
intMonth = intMonth + 12
End If
fncFiscalMonth = intMonth
Else
Err.Raise 5 'Invalid argument
End If

End Function

'----- end of function code -----
 
-----Original Message-----


If it helps, here are a couple of functions I use to extract the fiscal
year and fiscal month for a given date. These functions extract the FY
start month from a Profile table, but you could easily just hard-code
the value 7 (July) instead.

'----- start of function code -----
Function fncFiscalYear(InputDate As Variant) As Variant
' Given a date, returns the fiscal year it belongs in, based on the
' fiscal-year start month defined in the Profile.
'
' Copyright © 2002, Dirk Goldgar
' License is granted to use this code in your applications,
' provided the copyright notice remains intact.

Static intFYStart As Integer

' If we haven't already done so, get the start month of the fiscal
year
' from the Profile.
If intFYStart = 0 Then
intFYStart = fncProfileItem("FYStartMonth", 1)
End If

' Now use that information to calculate the fiscal year.
If IsNull(InputDate) Then
fncFiscalYear = Null
ElseIf IsDate(InputDate) Then
fncFiscalYear = Year(InputDate)
If intFYStart > 1 Then
If Month(InputDate) >= intFYStart Then
fncFiscalYear = Year(InputDate) + 1
End If
End If
Else
Err.Raise 5 'Invalid argument
End If

End Function

Function fncFiscalMonth(InputDate As Variant) As Variant
' Given a date, returns the month of the fiscal year in which it
falls,
' based on the fiscal-year start month defined in the Profile.

'
' Copyright © 2002, Dirk Goldgar
' License is granted to use this code in your applications,
' provided the copyright notice remains intact.

Static intFYStart As Integer

Dim intMonth As Integer

' If we haven't already done so, get the start month of the fiscal
year
' from the Profile.
If intFYStart = 0 Then
intFYStart = fncProfileItem("FYStartMonth", 1)
End If

' Now use that information to calculate the fiscal year.
If IsNull(InputDate) Then
fncFiscalMonth = Null
ElseIf IsDate(InputDate) Then
intMonth = (Month(InputDate) - intFYStart) + 1
If intMonth < 1 Then
intMonth = intMonth + 12
End If
fncFiscalMonth = intMonth
Else
Err.Raise 5 'Invalid argument
End If

End Function

'----- end of function code -----

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.
Thanks for the help. I'll give it a try and let you know
Click to expand...
how it works.
 
bgallagher said:
I am trying to set up a data access page with a pivot
table. Drill down includes Year, Quarter, Month, and
Day. The table is based on an Access Query. Is there an
easy way to change from calendar year display to fiscal
year display. Fiscal year starts July 1. Any help will
be appreciated, even if I can't change to FY.
Click to expand...

You might also try this code for fiscal year calculation, and how you'd use
it in a query, similar to Dirk's but a bit simpler:

intFMonth = the First Month of the Fiscal Year
in your case 7 for July

Function FY(dtDateIn As Date, intFMonth As Integer) As String
' ©Arvin Meyer 9/27/1997
On Error Resume Next

Dim intMonth As Integer
Dim intYear As Integer

intMonth = Month(dtDateIn)
intYear = Year(dtDateIn)
If intMonth >= intFMonth Then intYear = intYear + 1
FY = str(intYear)
End Function

The YearToDate would be:

Select DateField From Table1
Where FY([DateField],7)=FY(Date(),7);

A calculation for a full year would depend upon when you ran the query. You
would use:

Between FY([DateField],7) And [The ending date or an expression]

--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
-----Original Message-----
I am trying to set up a data access page with a pivot
table. Drill down includes Year, Quarter, Month, and
Day. The table is based on an Access Query. Is there an
easy way to change from calendar year display to fiscal
year display. Fiscal year starts July 1. Any help will
be appreciated, even if I can't change to FY.
Click to expand...

You might also try this code for fiscal year calculation, and how you'd use
it in a query, similar to Dirk's but a bit simpler:

intFMonth = the First Month of the Fiscal Year
in your case 7 for July

Function FY(dtDateIn As Date, intFMonth As Integer) As String
' ©Arvin Meyer 9/27/1997
On Error Resume Next

Dim intMonth As Integer
Dim intYear As Integer

intMonth = Month(dtDateIn)
intYear = Year(dtDateIn)
If intMonth >= intFMonth Then intYear = intYear + 1
FY = str(intYear)
End Function

The YearToDate would be:

Select DateField From Table1
Where FY([DateField],7)=FY(Date(),7);

A calculation for a full year would depend upon when you ran the query. You
would use:

Between FY([DateField],7) And [The ending date or an expression]

--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access


.
Thanks, I will give it a try and see what works best.
Click to expand...
 
gdfgdfgdf
-----Original Message-----


If it helps, here are a couple of functions I use to extract the fiscal
year and fiscal month for a given date. These functions extract the FY
start month from a Profile table, but you could easily just hard-code
the value 7 (July) instead.

'----- start of function code -----
Function fncFiscalYear(InputDate As Variant) As Variant
' Given a date, returns the fiscal year it belongs in, based on the
' fiscal-year start month defined in the Profile.
'
' Copyright © 2002, Dirk Goldgar
' License is granted to use this code in your applications,
' provided the copyright notice remains intact.

Static intFYStart As Integer

' If we haven't already done so, get the start month of the fiscal
year
' from the Profile.
If intFYStart = 0 Then
intFYStart = fncProfileItem("FYStartMonth", 1)
End If

' Now use that information to calculate the fiscal year.
If IsNull(InputDate) Then
fncFiscalYear = Null
ElseIf IsDate(InputDate) Then
fncFiscalYear = Year(InputDate)
If intFYStart > 1 Then
If Month(InputDate) >= intFYStart Then
fncFiscalYear = Year(InputDate) + 1
End If
End If
Else
Err.Raise 5 'Invalid argument
End If

End Function

Function fncFiscalMonth(InputDate As Variant) As Variant
' Given a date, returns the month of the fiscal year in which it
falls,
' based on the fiscal-year start month defined in the Profile.

'
' Copyright © 2002, Dirk Goldgar
' License is granted to use this code in your applications,
' provided the copyright notice remains intact.

Static intFYStart As Integer

Dim intMonth As Integer

' If we haven't already done so, get the start month of the fiscal
year
' from the Profile.
If intFYStart = 0 Then
intFYStart = fncProfileItem("FYStartMonth", 1)
End If

' Now use that information to calculate the fiscal year.
If IsNull(InputDate) Then
fncFiscalMonth = Null
ElseIf IsDate(InputDate) Then
intMonth = (Month(InputDate) - intFYStart) + 1
If intMonth < 1 Then
intMonth = intMonth + 12
End If
fncFiscalMonth = intMonth
Else
Err.Raise 5 'Invalid argument
End If

End Function

'----- end of function code -----

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.
Thanks for the help. I'll give it a try and let you know
Click to expand...
how it works.
 
Back
Top