Grouping Data in a Report by Fiscal Year

[pclutts]

I want to Group data in a report by Fiscal Year (for example, FY05 = 1 Oct 04 - 30 Sep 05, etc.). The Date field in my report is ProjectBeginDate which is currently formatted as a Short date (for example - 08/24/05). Can anyone out there tell me how to do that? Thanks in advance for any help.

 

[WayneRyan]

pclutts:

Add a new column to query. You can sort/group on FiscalYear.

Indented for readability:

FiscalYear: IIf(Format([SomeDate], "mmdd") > "0930", _  <-- If it's after September 30
                DatePart("yyyy", [SomeDate]) + 1,       <-- Add 1 year
                DatePart("yyyy", [SomeDate]))           <-- Otherwise keep the year.

Wayne

 

[pclutts]

Wayne,

My apologies, but could you show me how to add the field to the query; i.e. how to word it? Thanks again for your help.

 

[WayneRyan]

pclutts,

Change [SomeDate] to your date field, then just paste this in as a new
column in your query:

FiscalYear: IIf(Format([SomeDate], "mmdd") > "0930", DatePart("yyyy", [SomeDate]) + 1, DatePart("yyyy", [SomeDate]))

Wayne