Grouping times

[benlaird24]

Hi, I asked a question before concerning this same project and made some progress but have one more hangup. I am trying to just count the number of records that have a time field in one of four ranges:
6am-9am
9am-4pm
4pm-7pm
7pm-6am

I have used the total feature in the query design window to total some other fields, but the only way I know to total things in the range is to assign a value (like time1, time2, time3, time4) to the 4 ranges and total from that.

My problem is that I don't know how to assign a string to a range like that.
If i do a query for the 4 ranges, say assigning title1 to 6am to 9am, but make title2 9am to 4pm, it excludes everything because no time fits both of those criteria. If I make all the criteria 'OR' statements, every time is assigned to all four strings...

Does this make any sense? Any suggestions would be great...

 

[ReAn]

Make a new field like this:

Range: IIf(Hour([Timefield]) Between 6 And 9, 1, IIf(Hour([Timefield]) Between 9 And 16, 2, IIf(Hour([Timefield]) Between 16 And 19, 3, IIf((Hour([Timefield]) Between 19 And 24) OR (Hour([Timefield]) Between 0 and 6), 4, 0))))

It *should* work.

Edit: Forgot to mention, group by [Range]

 

[benlaird24]

I think that worked....thanks alot...never would have thought of that