Grouping report by calendar quarter

[Guest]

Several weeks ago, one of your experts (Steve Schapel) showed me how to
determine the quarter in which an employee becomes eligible for a particular
benefit. The formula Steve showed me was:
DateSerial(Year([HireDate])+1-(Month([HireDate])>9),(DatePart("q",[HireDate])*3+1)
MOD 12,1) It worked perfectly. (Thanks, Steve!) I set up a report to show
only those employees who are becoming eligible in the next upcoming quarter
by entering the next quarter's start date as a criterion in the query.

Now, we need to construct a report showing ALL employees, grouped by the
quarter in which they become eligible. I imagine I would need to set up the
Sorting and Grouping to group by quarter, but I can't figure out what field
to use as the basis of the group. Using the eligibility date doesn't give me
the proper grouping...I need something like 1st Quarter of 2000, 2nd Quarter
of 2000, etc. Can anyone give me a hand? Let me know if this isn't clear.
Thanks in advance!

 

[Rick B]

Wouldn't you just group on your formula...

DateSerial(Year([HireDate])+1-(Month([HireDate])>9),(DatePart("q",[HireDate]
)*3+1)
MOD 12,1)


Then in the group header, you could use if staments to say something like...

If the quarter is "1" then print "1st Quarter of " & the year.

 

[Guest]

Thanks, Rick! I'll give it a try!