Fiscal Year

[Cat]

I am building a DB for a group of users for fundraising. All the forms, queries and reports would be based on our Fiscal Year. Is there a way to set up the Fiscal Year dates first so that any forms, queries and reports, etc. "created" would automatically work with in the Fiscal dates?

Thanks for all suggestions.

 

[Rich]

Public Function FinancialYear(dDate As Date) As Integer
FinancialYear = Year(dDate) - IIf(dDate < DateSerial(Year(dDate), 5, 1), 1, 0)
End Function


the start of the fiscal year as shown here is 01/May

 

[Cat]

Thanks Rich

Now for the dumb question. Where is the best place to insert that expression?

 

[Rich]

In a query, where you want the output to display the fiscal year, base reports/graphs etc on the query/queries.
I forgot this part which also returns the fiscal week
Public Function FinancialWeek(dDate As Date) As Integer
FinancialWeek = (Date - DateSerial(FinancialYear(Date), 5, 1)) \ 7
End Function