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.

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

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.

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