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?