Reporting Total Time in dd:hh:nn:ss

[gumby]

I have four fields in my table.

TotalSessionTime
TotalProcessTime
TotalPrepTime
TotalWrapTime

These fields get populated with an afterupdate command on a form that
calculates the time between different fields. However, it my report I
would like to have a total rum of these times. It appears that if it
is over 24 hours, I get some wierd looking number that I don't
understand. Is thier any way to report our the totals in Time by
dd:hh:nn or even just hh:nn?

Thanks -

All the fields are Date/Time

 

[John Spencer]

The main part of the problem is that you are storing durations of time 8
hours and 10 minutes as points in time 8:10 AM.

You need to translate those into numbers of minutes or seconds. If you are
storing JUST the time, you can use some simple arithmetic to calculate the
equivalent durations in minutes or seconds.

Int(SessionTime*24*60) well give you minutes
Int(SessionTime*24*60*60) will give you seconds.

Once you have those you can Sum, average, Max, Min, etc..

With the total in minutes you can use an expression like the following to
display hours and minutes

Sum(Int(SessionTime*24*60)) \ 60 & ":" & Format(Sum(Int(SessionTime*24*60))
Mod 60,"00")

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[gumby]

The main part of the problem is that you are storing durations of time 8
hours and 10 minutes as points in time 8:10 AM.

You need to translate those into numbers of minutes or seconds.  If you are
storing JUST the time, you can use some simple arithmetic to calculate the
equivalent durations in minutes or seconds.

Int(SessionTime*24*60) well give you minutes
Int(SessionTime*24*60*60) will give you seconds.

Once you have those you can Sum, average, Max, Min, etc..

With the total in minutes you can use an expression like the following to
display hours and minutes

Sum(Int(SessionTime*24*60)) \ 60 & ":" & Format(Sum(Int(SessionTime*24*60))
Mod 60,"00")

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
.








- Show quoted text -

Thank you John. This worked great. I was thinking about also storing
these field values in seconds and the formating them in reports as
time. BUt still was confused on total seconds that equal greater than
24 hours.

 

[gumby]

Thank you John. This worked great. I was thinking about also storing
these field values in seconds and the formating them in reports as
time. BUt still was confused ontotalseconds that equal greater than
24hours.- Hide quoted text -

- Show quoted text -

Well I thought it was working, but it is promoting for a parameter for
Format when using the following expression in a text box.

=Sum(Int(SessionTime*24*60)) \ 60 & ":" &
Format(Sum(Int(SessionTime*24*60)) Mod 60,"00")

 

[John Spencer]

I don't see why that would happen unless you have misspelled Format or there
is a hidden character in the expression.

Did you perhaps cut and paste the expression from the posting? Sometimes
that introduces extraneous characters that cause problems. If you did cut
and paste, try typing in the expression instead.

The only other thing I can think of is that there is a references problem,
but that would usually generate an error of some type instead of generating
a parameter prompt.

To do its job, Access makes use of various external program and object
libraries. If you move a database from one machine to another, these
references may be "broken".

When this happens, you need to take steps to let Access repair the
reference(s) ON THE COMPUTER WHERE THE FAILURE IS OCCURING.

Here are MVP Doug Steele's instructions for how to do it:

*** Quote ***

Any time functions that previously worked suddenly don't, the first thing to
suspect is a references problem.

This can be caused by differences in either the location or file version of
certain files between the machine where the application was developed, and
where it's being run (or the file missing completely from the target
machine). Such differences are common when new software is installed.

On the machine(s) where it's not working, open any code module (or open the
Debug Window, using Ctrl-G, provided you haven't selected the "keep debug
window on top" option). Select Tools | References from the menu bar. Examine
all of the selected references.

If any of the selected references have "MISSING:" in front of them, unselect
them, and back out of the dialog. If you really need the reference(s) you
just unselected (you can tell by doing a Compile All Modules), go back in
and reselect them.

If none have "MISSING:", select an additional reference at random, back out
of the dialog, then go back in and unselect the reference you just added. If
that doesn't solve the problem, try to unselect as many of the selected
references as you can (Access may not let you unselect them all), back out
of the dialog, then go back in and reselect the references you just
unselected. (NOTE: write down what the references are before you delete
them, because they'll be in a different order when you go back in)

For far more than you could ever want to know about this problem, check out
http://www.accessmvp.com/djsteele/AccessReferenceErrors.html

Just so you know: the problem will occur even if the library that contains
the specific function that's failing doesn't have a problem.

**** End Quote ****

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

On Feb 8, 3:16 pm, "John Spencer" <[email protected]> wrote:

Well I thought it was working, but it is promoting for a parameter for
Format when using the following expression in a text box.

=Sum(Int(SessionTime*24*60)) \ 60 & ":" &
Format(Sum(Int(SessionTime*24*60)) Mod 60,"00")

 

[gumby]

I don't see why that would happen unless you have misspelled Format or there
is a hidden character in the expression.

Did you perhaps cut and paste the expression from the posting? Sometimes
that introduces extraneous characters that cause problems.  If you did cut
and paste, try typing in the expression instead.

The only other thing I can think of is that there is a references problem,
but that would usually generate an error of some type instead of generating
a parameter prompt.

To do its job, Access makes use of various external program and object
libraries.  If you move a database from one machine to another, these
references may be "broken".

When this happens, you need to take steps to let Access repair the
reference(s) ON THE COMPUTER WHERE THE FAILURE IS OCCURING.

Here are MVP Doug Steele's instructions for how to do it:

*** Quote ***

Any time functions that previously worked suddenly don't, the first thing to
suspect is a  references problem.

This can be caused by differences in either the location or file version of
certain files between the machine where the application was developed, and
where it's being run (or the file missing completely from the target
machine). Such differences are common when new software is installed.

On the machine(s) where it's not working, open any code module (or open the
Debug Window, using Ctrl-G, provided you haven't selected the "keep debug
window on top" option). Select Tools | References from the menu bar. Examine
all of the selected references.

If any of the selected references have "MISSING:" in front of them, unselect
them, and back out of the dialog. If you really need the reference(s) you
just unselected (you can tell by doing a Compile All Modules), go back in
and reselect them.

If none have "MISSING:", select an additional reference at random, back out
of the dialog, then go back in and unselect the reference you just added. If
that doesn't solve the problem, try to unselect as many of the selected
references as you can (Access may not let you unselect them all), back out
of the dialog, then go back in and reselect the references you just
unselected. (NOTE: write down what the references are before you delete
them, because they'll be in a different order when you go back in)

For far more than you could ever want to know about this problem, check outhttp://www.accessmvp.com/djsteele/AccessReferenceErrors.html

Just so you know: the problem will occur even if the library that contains
the specific function that's failing doesn't have a problem.

**** End Quote ****

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
.




Well I thought it was working, but it is promoting for a parameter for
Format when using the following expression in a text box.

=Sum(Int(SessionTime*24*60)) \ 60 & ":" &
Format(Sum(Int(SessionTime*24*60)) Mod 60,"00")

It was a missing reference. Odd, but it solved it. It was Missing the
Excel Reference.

Thanks - David