Grouping records in a report by multiple years

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to create a report that groups events biannually. Thus all
events in the current and last year would appear in the first group, 2 and 3
years ago in the 2nd group, etc. I used sort/group on the date field using
the year and set the interval at 2, but the report came out with the 1st
group consisting of only the current year, and the 2nd group consisting of 1
and 2 years ago combined, a 3rd group consisting of 3 and 4 years ago, etc.
How do I get the first group to reflect the current and past year?
 
ElusiveRich said:
I am trying to create a report that groups events biannually. Thus all
events in the current and last year would appear in the first group, 2 and 3
years ago in the 2nd group, etc. I used sort/group on the date field using
the year and set the interval at 2, but the report came out with the 1st
group consisting of only the current year, and the 2nd group consisting of 1
and 2 years ago combined, a 3rd group consisting of 3 and 4 years ago, etc.
How do I get the first group to reflect the current and past year?
Click to expand...

Instead of grouping on the date field with that interval
stuff, group on the expression:

=(Year(Date()) - Year([datefield])) \ 2

Note the integer divide operator \
 
It worked perfectly!

Marshall Barton said:
ElusiveRich said:
I am trying to create a report that groups events biannually. Thus all
events in the current and last year would appear in the first group, 2 and 3
years ago in the 2nd group, etc. I used sort/group on the date field using
the year and set the interval at 2, but the report came out with the 1st
group consisting of only the current year, and the 2nd group consisting of 1
and 2 years ago combined, a 3rd group consisting of 3 and 4 years ago, etc.
How do I get the first group to reflect the current and past year?
Click to expand...

Instead of grouping on the date field with that interval
stuff, group on the expression:

=(Year(Date()) - Year([datefield])) \ 2

Note the integer divide operator \
Click to expand...
 
Back
Top