Group Calculated Time in a Report

[CDG via AccessMonster.com]

I work in a hospital an am creating a database to record wait times of
patients. I created a field in the query that is attached to my continous
form to calculate the time. Time: ([time called]-[time checked in]) The
user enters the time in military time; this aids in the calculated field.
For example, time called (13:15) - time checked in (13:05) = time 10).
This field works fine. However, now I need to group the patients that waited
a specific time in a report. All patients that waited between 0 through 5
minutes should be grouped together, all patients that waited 6 through 10
minutes should be grouped together and so on. However, the grouping is not
working. It appears the calculated time field is being stored in decimals.

Any suggestions? Thank you,
CDG

 

[Marshall Barton]

I work in a hospital an am creating a database to record wait times of
patients. I created a field in the query that is attached to my continous
form to calculate the time. Time: ([time called]-[time checked in]) The
user enters the time in military time; this aids in the calculated field.
For example, time called (13:15) - time checked in (13:05) = time 10).
This field works fine. However, now I need to group the patients that waited
a specific time in a report. All patients that waited between 0 through 5
minutes should be grouped together, all patients that waited 6 through 10
minutes should be grouped together and so on. However, the grouping is not
working. It appears the calculated time field is being stored in decimals.

Right, a date/time field is stored as a double precision
floating point number.

Note that your "working" time calculation will not work if
the interval crosses the midnight boundary. Your field
really should contain both the date and time parts so you
don't have to worry about midnight.

Subtracting one date/time value from another can work, IF
you know exactly what you are doing with the floating point
dste/time value, but you really should use the DateDiff
function for this kind of thing. For example,
DateDiff("n", [Time called], [Time Checked In])
should give you the number of minutes (if you are not too
worried about gractions of a minute).

You can use the DateDiff calculation (preceeded by =) in the
report's Sorting and Grouping and set the Interval to 5
(minutes).

 

[CDG via AccessMonster.com]

This is not working. The time is not subtracting. All that is diplaying is
0:00.

I work in a hospital an am creating a database to record wait times of
patients. I created a field in the query that is attached to my continous

[quoted text clipped - 6 lines]

minutes should be grouped together and so on. However, the grouping is not
working. It appears the calculated time field is being stored in decimals.

Right, a date/time field is stored as a double precision
floating point number.

Note that your "working" time calculation will not work if
the interval crosses the midnight boundary. Your field
really should contain both the date and time parts so you
don't have to worry about midnight.

Subtracting one date/time value from another can work, IF
you know exactly what you are doing with the floating point
dste/time value, but you really should use the DateDiff
function for this kind of thing. For example,
DateDiff("n", [Time called], [Time Checked In])
should give you the number of minutes (if you are not too
worried about gractions of a minute).

You can use the DateDiff calculation (preceeded by =) in the
report's Sorting and Grouping and set the Interval to 5
(minutes).

 

[Marshall Barton]

This is not working. The time is not subtracting. All that is diplaying is
0:00.


Marshall Barton wrote:
you really should use the DateDiff

function for this kind of thing. For example,
DateDiff("n", [Time called], [Time Checked In])
should give you the number of minutes (if you are not too
worried about gractions of a minute).

You can use the DateDiff calculation (preceeded by =) in the
report's Sorting and Grouping and set the Interval to 5
(minutes).

Of course, the result of DataDiff is, as I said before, "the
number of minutes". It is not a date/time value so using a
time kind of format will not display the value as you want.
Try just removing whatever you have in the text box's Format
property.