Grouping by Time in Report form

[WM]

I have created a call query for a specific date with call times range from
2:00 PM to 5:00 PM. When creating the report, I selected to group calls by
hour and created a formula to count calls.
=Format$([cTime],"h ampm",0,0) is the formula used in the header.
Group 1 includes calls from 2:00 - 2:30
Group 2 includes calls from 2:31 - 3:30
Subsequent groups include calls for the hour beginning on the half hour.

Is there a different formula or format I can use to produce either call
groups on the 1/2 hour or hour but not both?

 

[Ken Sheridan]

You could use integer division to determine which half-hour the time falls in:

Format[cTime],"hh") & Minute [cTime])\30

This would return 070 for 7:10 AM, 071 for 7:40 AM for example enabling you
to group the report by each half-hour. Note, however, that the half-Hours
would run from ##:00 to ##:29 and ##:30 to ##:59.

You can of course use the same expression to group by any division of an
hour, so you could group by 15 minute slots for instance with:

Format[cTime],"hh") & Minute([cTime])\15

If you wanted to group by a period of 10 divisions per hour or more you'd
have to format the result of the integer division, e.g. to group by 5 minute
slots:

Format[cTime],"hh") & Format(Minute([cTime])\5,"00")

While the values returned by the above expressions would be fine for
grouping the report to display the start time of each time slot in a
recognizable time format you'd use the following as the ControlSource of a
text box in the group header:

Format([cTime],"hh") & ":" Format((Minute([cTime])\30)*30,"00")

Ken Sheridan
Stafford, England