Access Fiscal Year Seminar

Access Fiscal Year Seminar
By Richard Rost

9 years ago

Well, I wasn't planning on making this a seminar, but at least three different customers asked me about this over the past two weeks, and one of them really needed to learn how to do it, and he submitted it as a PAID TechHelp question. So... I decided to turn it into a short seminar.

This Fiscal Year Seminar will teach you how to perform calculations in your Access databases based on a company's fiscal year. You will...

1. Learn how to calculate a simple fiscal year (starting 6/1, for example)
2. Create sales totals with an aggregate query or report grouping
3. Calculate fiscal year not based on the 1st of a month
4. Determine fiscal year for multiple companies in the same database

You can watch the free introductory and review lessons, and learn more about this seminar here: Microsoft Access Fiscal Year Seminar

When your fiscal year starts Upload Images Link
Bruce Reynolds
9 years ago

The VBA function as defined in 06:26 of this video may not be taking the valid approach. I think the function should be redefined as follows:

Public Function MyFiscalYear(DateIn As Date) As Integer

    MyFiscalYear = Year(DateIn)

    If Month(DateIn) > 5 Then
       MyFiscalYear = MyFiscalYear + 1
    End If

End Function

This way, if the fiscal year BEGINS on June 1st, 2012, then you are STARTING fiscal year 2013.

Reply from Richard Rost:

Bruce, that is a perfectly valid argument. It all depends on your needs and when your fiscal year starts. I've had clients who had their fiscal year 2012 run from 6/1/11 to 5/31/12, and I've had others that defined FY 2012 as 6/1/12 to 5/31/13. It's all up to you and how you want to define it for your business.

The Federal Government, for example uses a FY that ENDS in the current year, so FY 2013 is from October 1 2012 through September 30 2013. It runs from October 1 of the PRIOR year through Sept 30 of the current year.

Show All Comments