Creating Fiancial Year rather than Current Year 1

[MySki]

I am working in a database where I am projecting out 4 years from the current year. For example CurrentYear = 2006, CurrentYear+1 =2007, etc. This works great for the calendar year. However, our Fiscal Year runs from October 1 - September 30. Can I assign this range to my current year so on October 1 of 2006 my report will change to 2007? I need this to be in a universal module so it affects every instance of it in my database.

Thanks!

 

[Golom]

Sounds like Fiscal Year is a dynamic quantity. You might build your own UDF to return the value whenever you call it with some calendar date

Public Function FiscalYear(SomeDate As Date) As Long

FiscalYear = Year(SomeDate) + IIf(Month(SomeDate) >= 10,1,0) 

End Function

 

[MySki]

Golom

Sorry, but I'm trying to fill in the blanks with no success. FY 2006 would be 10/01/05-09/30/06. FY 2007 would be 10/01/06-09/30/07. Using your above funcion, how would I assign the values for the fiscal year.

 

[Golom]

Supply a calendar date to the function and it returns the Fiscal year for that date. For Example

FiscalYear (#11/22/2006#)

returns 2007 and

FiscalYear (#09/22/2006#)

returns 2006.

If you want the converse (i.e. a date range when you supply the year) then

Public Function FiscalYearRange(FYear As Long) As String
FiscalYearRange = Format(DateSerial(FYear-1,10,1),"mm/dd/yyyy") & "-" & _
                  Format(DateSerial(FYear,9,30),"mm/dd/yyyy")
End Function

 

[IT4EVR]

Golom has a good approach. This is just a slightly different way of doing it. I create a public module and create two functions that return the beginning fiscal date and the ending fiscal date

Public Function getBeginningFY(ByVal lngYear) As Date
    getBeginningFY = DateSerial(lngYear - 1, 10, 1)
End Function
Public Function getEndingFY(ByVal lngYear) As Date
    getEndingFY = DateSerial(lngYear, 9, 30)
End Function

You can then access these in code or in a query:

PARAMETERS [Forms]![frm_Charts_List]![cboFiscalYear] Long;
SELECT (Format([Date_Completed],"MMM 'YY")), Count([ID]) AS CountOfID
FROM tbl_Data
WHERE Date_Completed Between getBeginningFY(Forms!frm_Charts_List!cboFiscalYear) And getEndingFY(Forms!frm_Charts_List!cboFiscalYear) 
GROUP BY (Year([Date_Completed])*12+Month([Date_Completed])-1), (Format([Date_Completed],"MMM 'YY"));