Get fiscal year to show in a report

  • Thread starter Thread starter David McCormack
  • Start date Start date
D

David McCormack

I have a database that I'm trying to get a report to sort on my fiscal year.
I have a module that I set up (below) to set the dates for my fiscal year.

Function FiscalYear() As Date
Dim dtFiscalYear As Date
Dim strFiscalYear As String
Dim intMonth As Integer
intMonth = Month(Date)
'this will tell you the current month
If intMonth < 11 Then
strFiscalYear = "11/1/" & Year(Date) - 1
Else
strFiscalYear = "11/1/" & Year(Date)
End If
dtFiscalYear = CDate(strFiscalYear)
FiscalYear = dtFiscalYear
End Function

I then add the "Between FiscalYear() And Date()" to the date fields in my
queries. the queries are working great. My issue is that I'm trying to get
my reports to give the year-to-date based on the fiscal year but they keep
breaking them down to the calendar year. How do I set the sort feilds in the
report to sort by my fiscal year of Nov 1 to Oct 31?
 
David

Although you are converting a date (e.g., 11/1) from one 'year' to another
to get something you are calling a FiscalYear, it is still a date/time
value.

If you need to know the year (i.e., the "Fiscal Year"), why not just use the
Year instead of the entire date?

Check Access HELP for the Year() function.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
Jeff,

I'm not trying to convert the year information per sai. I am trying to show
a year-to-date report that will summerize the information by the fiscal year
which runs 11/01 through 10/31. The dates remain as a date/time field, I
just need to group them as stated above.
 
Jeff,

I guess I should have titled this differently. It should have said "Get a
report to sort/group by a fiscal year."
 
I'm not sure if this works for your situation but a nice way to deal with
fiscal type calculations is to have a
time dimension table. Where you have one record for each day of the year
for as many years as you need.
It's a common data warehousing technique.

So for example you could have columns for date, fiscal year, fiscal month,
fiscal week, etc....
Usually you would run some kind of code to create this table (one time
setup). I did a quick search and didn't find a great bit of code but this
might give you the idea:
http://www.sqlbook.com/Data-Warehousing/Date-Dimension-SQL-script-18.aspx

Then you just always join this table into the mix (by joining by date) to
help with knowing what fiscal year, fiscal month, fiscal quarter, holiday or
all kinds of other type of information (day of the year etc...).

You other approach is to do calculations in the query itself but they get to
be a real pain if you have to do it too often.
iif(Month(TheDate) >= 11, .........)

HTH,
 
Back
Top