Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Edinburgh, Midlothian, Scotland
    Posts
    492
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Grouping Reports by Year (Access 2k2)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Grouping Reports by Year (Access 2k2)

    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.

  3. #3
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Edinburgh, Midlothian, Scotland
    Posts
    492
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Grouping Reports by Year (Access 2k2)

    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.

  4. #4
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Edinburgh, Midlothian, Scotland
    Posts
    492
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Grouping Reports by Year (Access 2k2)

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

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Grouping Reports by Year (Access 2k2)

    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 <img src=/S/grin.gif border=0 alt=grin width=15 height=15>).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •