Grouping on Time 1

[Knicks]

Access 2000

I have a database that tracks clients by time in a waiting room. Essentially clients are seen in 3 shifts, 9am-1pm, 1pm-6pm, and 6pm-midnight. This is accomplished using 2 fields, DateI (date in using Date()) and TimeI (time in using Time())

I need to make a report that groups at 2 levels, the first level being Day of the week (Monday, Tuesday, ect..). This was easy to do using the Format$([dateI],"dddd") in the underlying query. I would then like to group on time using the 3 shifts mentioned above. I tried using "between" in the format function for time but I got a syntax error.

What would be the best way to create those 3 shift groups? Should it be done in VBA in the TimeI group header? Or is there a way in the underlying query? I don't want to limit my results in the criteria portion of the query but rather create an expression like AM, PM, Evening usinng some form of between.

Any advice would be welcomed
Thanx in advance

 

[dhookom]

You could group by your day of week followed by:
Switch(TimeI < #13:00#,"1st",TimeI<=#18:00#,"2nd",TimeI>#18:00#,"3rd")

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Knicks]

Thanx!

That worked perfectly, I'll have to research this "switch" function...very handy for organizing reports and satisfying zany user requests!