I have a report that calculated the elapsed time based on user input of startTime and endtime.
The report uses an Instant If to display the elapsed time for each event grouped by the person participating.

I need to total the elapsed time and have it displayed as HH:MM.

I have a Running Total box in the group footer, but it's not giving me accurate totals.

Here's an example:
Name date start end elapsed
Doe, John 1/1/10 1000 1100 1:00
1/2/10 1415 1430 0:15

total 0:15

Next Group

My Running Sum is set to Overall.
What is the secret to adding time?
Thanks in advance!

Erick

Erick

I'm going to guess that you have that control/field specified as a Date/Time datatype. In Access, Date/Time data is "point-in-time", not "duration".

So right now, you can take the difference between a Start and End and format it as HH:MM using the Date/Time field ... but check out what happens if you run longer than 24 hours. It resets to zero! (actually, it doesn't, but if you're only seeing HH:MM, it looks like it does).

The common approach to calculating duration is to:
1) calculate the different in the lowest unit of measure (looks like minutes in your situation)
2) sum up the (minutes) lowest units
3) convert that total to your preferred display format (e.g., HH:MM) by doing the calculations. 61 minutes, for example, is 1 hour & 1 minute.

Good luck!

Hi Erick

In addition to Jeff's comments, whatever calculation you do, you will want to total this in the report. Rather than try to use running sum in the footer, you can create a hidden text box in the detail section as a running sum and then report its value in the footer. This should work for the example given although, as Jeff has pointed out, it would give incorrect totals if the sum is over 24.

Remember that a time in a date/time field is stored as a fraction of a day, so that it is often useful to multiply the time by 24 to convert into a numeric value of hours before performing your calculations.

HTH
David

This post has been edited by David Peel: Jul 30 2010, 11:04 AM

Okay, instead of getting wrapped around the axle on the issue of calculating "time", I have decided to simply display it as a number and call it time.
This will work for me since we use a 24hr clock here anyway.

However, I cannot seem to get the report to give me subtotals by group. I think it's because the actual numbers that need to be subtotaled are not calculated until the report runs (using Instant If).

I have got the sort/group settings configured, but it doesn't display anything when the report runs.

Any thoughts?

Erick

You don't mention "how" you are trying to get "subtotals by group". It's a bit tough to diagnose without more info...

With the report in design view, I opened Group/Sort.
I selected my grouping level, then clicked "more".
I clicked on "totals" and selected the field that I wanted totaled.
I selected to show the result in the group footer of the report.

The field appears in the design view (and I made sure the font was different from the background), but no totaling is happening.

The field I need totaled is not populated until the report runs.

Erick

Hi Erick

Are you able to attach a copy of your database? If not, then at least detail your calculations, including field names and datatypes, etc. and we may be able to understand what you are trying to do and what is happening to prevent it. There are many different scenarios based on what you have said so far.

David

Okey-Dokey

Here's the database (with test data).

Please let me know if you have any questions.

Erick

Hi Erick

To make your subtotal work in the report, just copy the calculation in totalVisitTime in the detail section into the AccessTotalstotalVisitTime text box in the footer, enclosing it in a sum function (=Sum([visitEndTime]-[visitStartTime])).

As you have discovered, Access will not sum a calculated control in this way, but if you have a simple calculation, you can usually repeat it inside the sum function within the group footer.

You could also think about doing the calculation in a query, in which case the report should have no problem in performing the sum on the named column from the query.

NB: As stated earlier, this calculation will only work for totals up to 24 hours. If you are likely to need to total times exceeding 24 hours, then you would need to calculate in a different way.

HTH
David

Thanks a ton!!

Works like a charm!

Erick