Report Wizard - Group by - Fiscal Year

[Gene]

I used the Report Wizard and grouped by QTR (ofcourse this starts with Jan).
I noted that the Function used was Format$.

My Question: my company uses a fiscal year starting with July. So, my QTR1 I
would like to group by July-Sept. Can I modify the Format$ Function to make
this happen? If not, HOW can I make this happen?
MUCH THANKS!
Gene

 

[Ken Sheridan]

Gene:

To adjust the start of the year you just need to subtract 6 months from the
date, but you'll also need to extend the expression quite a lot to show the
right fiscal year. Try this (as a single line in the ControlSource property;
it will have split in your newsreader)

=Year(DateAdd("m", -6,[TransactionDate])) & "/" & Format(DateAdd("m",
6,[TransactionDate]),"yy") & " Q" & Format(DateAdd("m",
-6,[TransactionDate]),"q")

substituting your real field name for TransactionDate. This should give the
quarter in a format such as:

2007/08 Q4
2008/09 Q1
2008/09 Q2
2008/09 Q3
2008/09 Q4
2009/10 Q1

beginning on 1 July each year.

Ken Sheridan
Stafford, England

 

[Gene]

Ken
THANKS!
I will give it a try! I'm SURE it will work!
THANKS!
Gene