Interval Reporting

[mlopez]

Hello everyone,
I work at a call center and I am trying to make an interval report using our individual call records. The easiest way I figured to do this in access was using

Int([Dialer Results]![Time]*48/48)

as an expression to convert my existing timestamp into the nearest whole time (Ex. 9:36:45 AM to 9:30 AM) and a column "interval" or "Time Block" to see what block the call was made on. This works fine in excel but when I try doing this in my Access query I get #Error for all my records. I've also tried

Format(Int(([Dialer Results]![TIME]*48)/48),"h:nn AM/PM")

with no luck. Any help would be appreciated, I'm also open to changing the way I build this Interval Report as I am a mere novice.

[ssanfu]

Welcome to the forum mlopez.

The first thing you should know is that "Time" is a reserved word in Access and shouldn't be used as an object name.
See: http://allenbrowne.com/AppIssueBadWord.html

Also, you shouldn't use spaces or special characters in object names, only letters and numbers.
See: http://access.mvps.org/access/tencommandments.htm

If you are trying to do this conversion in a query, try:

Interval: TimeSerial(Hour([sTime]),Minute([sTime]),0)

where "sTime" is the name of the time field.

[mlopez]

Thanks for your response.
I will definitely take all your suggestions into consideration. Using the formula you have me was helpful but what I received in return was more 9:36:41 AM being converted into 9:36:00 AM. I needed it to turn into 9:30 AM. Anything else you think of?

Welcome to the forum mlopez.

The first thing you should know is that "Time" is a reserved word in Access and shouldn't be used as an object name.
See: http://allenbrowne.com/AppIssueBadWord.html

Also, you shouldn't use spaces or special characters in object names, only letters and numbers.
See: http://access.mvps.org/access/tencommandments.htm

If you are trying to do this conversion in a query, try:

Interval: TimeSerial(Hour([sTime]),Minute([sTime]),0)

where "sTime" is the name of the time field.

[ssanfu]

This will give you ten minute intervals. 30 to 39 minutes returns 30; 40 to 49 returns 40, ...

Interval: TimeSerial(Hour([sTime]),Int(Minute([sTime])/10)*10, 0)

To get the nearest ten minutes, use

Interval: TimeSerial(Hour([sTime]),Int((Minute([sTime])+5)/10)*10, 0)

so 25 to 34 returns 30, 35 to 44 returns 40, etc