How do I set the Fiscal Year in Access 2002?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Can anyone answer how to set the Fiscal Year in a database. I selected
"quarterly" when using a query to create a report. It however returns the
last quarter for 2004 while I need this to be the first quarter of 2005. Is
there a way to set this type of date via a module?
 
GrouchyMedic said:
Can anyone answer how to set the Fiscal Year in a database. I selected
"quarterly" when using a query to create a report. It however returns the
last quarter for 2004 while I need this to be the first quarter of 2005. Is
there a way to set this type of date via a module?
Click to expand...
 
Can anyone answer how to set the Fiscal Year in a database. I selected
"quarterly" when using a query to create a report. It however returns the
last quarter for 2004 while I need this to be the first quarter of 2005. Is
there a way to set this type of date via a module?
Click to expand...

How is your fiscal year defined? October 1 to October 1? If so, you
can use

FiscalYear: Year(DateAdd("m", [datefield], 3))
FiscalQuarter: DatePart("q", DateAdd("m", [datefield], 3))

The dateadd function will add three months to the date/time value, so
October 1 2004 will become January 1 2005; extracting the year or the
quarter from this modified date will give the fiscal year and fiscal
quarter.

John W. Vinson[MVP]
 
GrouchyMedic said:
Can anyone answer how to set the Fiscal Year in a database. I selected
"quarterly" when using a query to create a report. It however returns the
last quarter for 2004 while I need this to be the first quarter of 2005. Is
there a way to set this type of date via a module?
Click to expand...

This is a function for non-calendar fiscal years.
intFMonth = the First Month of the Fiscal Year

Function GetFY(dtmDate As Date, intFMonth As Integer) As String
' ©Arvin Meyer 9/27/1998
On Error Resume Next

Dim intMonth As Integer
Dim intYear As Integer

intMonth = Month(dtmDate)
intYear = Year(dtmDate)

If intMonth >= intFMonth Then intYear = intYear + 1

GetFY = "FY" & str(intYear)

End Function
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
Thanks, Arvin. I will give it a try. Murray

Arvin Meyer said:
This is a function for non-calendar fiscal years.
intFMonth = the First Month of the Fiscal Year

Function GetFY(dtmDate As Date, intFMonth As Integer) As String
' ©Arvin Meyer 9/27/1998
On Error Resume Next

Dim intMonth As Integer
Dim intYear As Integer

intMonth = Month(dtmDate)
intYear = Year(dtmDate)

If intMonth >= intFMonth Then intYear = intYear + 1

GetFY = "FY" & str(intYear)

End Function
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
Click to expand...
 
Thanks, John. Have received to helpful hints on a perplexing problem. Intend
to try yours and the other also. Murray

John Vinson said:
Can anyone answer how to set the Fiscal Year in a database. I selected
"quarterly" when using a query to create a report. It however returns the
last quarter for 2004 while I need this to be the first quarter of 2005. Is
there a way to set this type of date via a module?
Click to expand...

How is your fiscal year defined? October 1 to October 1? If so, you
can use

FiscalYear: Year(DateAdd("m", [datefield], 3))
FiscalQuarter: DatePart("q", DateAdd("m", [datefield], 3))

The dateadd function will add three months to the date/time value, so
October 1 2004 will become January 1 2005; extracting the year or the
quarter from this modified date will give the fiscal year and fiscal
quarter.

John W. Vinson[MVP]
Click to expand...
 
Back
Top