Reports to show fiscal year

[Lifeseeker]

Hi,

Is there a way to get a report to show dates in a fiscal year format? A fiscal year is April 1st to March 31st.

right now users apply filters directly in the report. So they right click on a date field and pick "this year" from the selection. The report, however, filter results in a normal year sense.

Is it possible at all?

Comment/help much appreciated.

 

[PaulO]

You perhaps need a 'Calendar' table with two fields (Date; Fiscal year) and 365 records for each year (except leap years of course).

e.g.
01/04/2011;2011
02/04/2011;2011
31/03/2012;2011
01/04/2012;2012
etc

Link this table into your existing source query for the report, and drag the Fiscal year field into the report.

If you want to 'future-proof' the new table you could add two additional fields, perhaps, being Month and Week
e.g.
01/04/2011;2011;1;1
15/05/2011;2011;2;7
31/03/2012;2011;12;52
01/04/2012;2012;1;1
etc

 

[Brianwarnock]

This has been answered on the forum before, and I think that there is info in the tutorials or code examples so I suggest a search.
You can use Google to improve the search there is a sticky on that in the General section.

Brian

 

[HiTechCoach]

It is possible to calculate the fiscal year date range.

See:
HOW TO: Get the Fiscal Year or Month of a Particular Date in Access 2000

 

[plog]

Here's what I use:

FiscalYear: Year([YourDateFieldHere]) + iif(Month([YourDateFieldHere])>3, 1,0)

 

[smoothtracks]

Where do I put this