July-June Fiscal Year, Not Jan-Dec

[blazerboy6]

I'm having trouble getting Access 2007 to return a formatted qry/rpt for our fiscal quarter. The report runs fine, except we have to know that Q1 2011 on the report actually means our own Q3 2010/2011.



The current expression in the qry is this:

rmDate By Quarter: Format$([tblReportingMatrix].[rmDate],'\Qq yyyy')

Do I need to write a complex SQL statement or something of that nature, to define Q1 as between July 1 and Sept 30, etc? Is there a simpler way? Help!

[ajetrumpet]

July 1 - Sept 30 is quarter #3 to computers, so why not just use a DATEADD() function and use the "q" parameter in the form timescale argument?? You could put an IIF() statement in it as well, just in case the quarter the computer is on is > 2. If so, it needs to circle back to 1 or 2 for you.

[blazerboy6]

ajetrumpet,

Thanks for the recommendation of DateAdd, I didn't have to worry about an iif statement since I wanted the first two quarters to group with the next calendar year. Here's what I came up with:

rmDate By Quarter: Format$(DateAdd("q",2,[tblReportingMatrix].[rmDate]),'\Qq') 
& " " & Format$(DateAdd("q",-2,[tblReportingMatrix].[rmDate]),'yyyy') 
& "-" & Format$(DateAdd("q",2,[tblReportingMatrix].[rmDate]),'yyyy')

This now gives me the correct result of:
'Q1 2011-2012' for a date of '7/12/2011' instead of 'Q3 2011'.

Thanks for your help.