Summing Time is giving me wrong totals.

[Nuke1096]

Ok, I'm stumped on this one. I'm trying to create a query that will sum the total time of a specific field. It seems to be doing it, however the value is off by by a couple minutes on all my examples and I cant seem to figure out why. Let me break it down how I'm doing it...



Table

- BusArrivalTime
- BusDepartTime



Query


In my Query I'm making a new field like this. It correctly figures out the difference.

BusWaitTime: DateDiff("n",[BusArrivalTime],[BusDepartTime])



Report

Next I'm displaying that Query information inside of a Report by putting this in the Text Box on the Report. It correctly displays the time in the Hours/Minutes format.

=[BusWaitTime]\60 & Format([BusWaitTime] Mod 60,"\:00")


Lastly, I'm using another Text Box on the Report to Sum the Grand Total of the Wait Time for all my records. Here is what Im putting in the Control Source

=Sum([BusWaitTime])\60 & Format([BusWaitTime] Mod 60,"\:00")

...and it's summing my records, but the value is off by a few minutes and I cant figure out why.


In my example, I have 3 records with times of 3:14, 1:35, and 3:20. It should be totaling a figure of 8:09 but its coming to 8:14 instead. Any suggestions?

[pbaldy]

You're summing in the hours but not in the minutes. It's likely using the last record's minutes.

[Nuke1096]

Wouldnt it come out as 7:14 instead of 8:14 in that case? In any event though, how would I sum the minutes into that syntax, alongside the hours?

[pbaldy]

Wrap the field in the Sum() function just as you did in the hours calculation.

[Nuke1096]

I tried doing that by doing...

=Sum(Hour([BusWaitTime])+Minute([BusWaitTime]))

...but it's now just giving me 0 as a sum.

[pbaldy]

=Sum([BusWaitTime])\60 & Format(Sum([BusWaitTime]) Mod 60,"\:00")

[Nuke1096]

Ohhh ok I see now. Yup that did it! I hate working with Time/Dates!!!!

Thanks a ton!

[pbaldy]

Happy to help!