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 - 22 Nov 2004 21:28 GMT
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.
> 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
[quoted text clipped - 7 lines]
> of 2000, etc. Can anyone give me a hand? Let me know if this isn't clear.
> Thanks in advance!
Susan - 23 Nov 2004 13:45 GMT
Thanks, Rick! I'll give it a try!