Grouping Reports by Year (Access 2k2)

Topic

New Reply

Is there an easy way to choose a date for grouping a report by other than 1st January – 31st December.

I’d like to group all entries from 1st July to 30th June for example, as this ties in with a financial year end.

I’m sure that eventually, I could produce some code myself to do it, but I was hoping that someone already has done so.

Thanks

Reply | Quote

Replies

If your report is based on a query, you can add a calculated field for Fiscal year.
If your report is based on a table, you can create a query based on that table and do the same.

The definition of the calculated field can be:

FiscalYear: Year([MyDate])+(Month([MyDate])<7)

where MyDate is the date field you want to group.

I've used a little trick to avoid the use of the IIf function (it is slow and prevents compilation of the query). If the month is less than 7 (that is January through June), Month(…)<7 is True, which evaluates to -1, so 1 is subtracted from the year. If the month is 7 or more (July through December), Month(…)<7 is False, which evaluates to 0, so the year is not modified.

Note: if you don't want to create or modify a query, you can also enter the expression for fiscal year into the Grouping and Sorting window of the report. Enter

=Year([BillDate])+(Month([BillDate])<7)

in =to the Field/Expression box, and set Group header and/or Group footer to Yes.

Reply | Quote

What a nice easy solution. Thanks

It worked brilliantly. Tried it both ways, and each works well. I might even try leaving its as a query, since that would give me a choice of fiscal or calendar year.

Thanks

PS only minor point for anyone using this. If entering it straight into the Grouping and Sorting window of the report, you need to change the Group on value to “Each Value”, since this in strictly no longer a “Year”, but a numeric value. This puzzled me for a bit till I worked out why the report wasn’t changing.

Reply | Quote

Why can’t i think of answers like this myself????????

Reply | Quote

That’s what I often think when I see answers given by others. One of the great strengths of the Lounge is that often, Loungers will come up with surprisingly simple solutions to seemingly complex problems (and sometimes with surpisingly complex solutions to seemingly simple problems ).

Reply | Quote