Total Time in a Report

[unclefink]

I have done this before in a so far successfull database but am now having problems.

I have a report which indicates what time someone checked something out and what time they checked it back in. I need to get the total Hours the item was checked out. When I do this using the same method I did on the first database it works. When I do it on the db I am currently working on I get an error indicating "Date Type Mismatch in Criteria Expression". The syntax i am applying in the unbound field is =sum([Total Hours]), anyone have any ideas as to how I can fix this?

 

[vbaInet]

How is the Total Hours field derived?

 

[unclefink]

("Field1")EndDateTime-("Field2")StartDateTime=("Calculated Field in Query")Total Hours: Format([EndDateTime]-[StartDateTime],"Short Time")

This part is working fine, What I am trying to do is create a query or report to total all "Total Hours" for each individual person.

 

[vbaInet]

For next time, please write your code one line at a time.

You cannot sum on a calculated field. The calculation needs to be in the Record Source of your report so move it to your query.

 

[unclefink]

OK, still on the task at hand. I tried rounding the times to the nearest quarter hour and then adding those up and it worked. The problem with that is I dont want the times to round to the nearest quarter hour. I want the exact total time someone is doing something in real time. Example: 4:17 minutes is showing as 4.25 and 4:03 is showing as 4:00. I'm getting total times for each record, the problem i'm having is totaling those total times on a report to show the overall total time a specific person is doing something.

 

[vbaInet]

So you Sum([EndDateTime] - [StartDateTime])

 

[unclefink]

So i'm getting closer and closer however am still having difficulty understanding what is going on here. Some of the times are adding up correctly and some of them are just way off base.

If you view the attachment, you'll see that the "Total Time's" for employee numbers 1, 2, 5,7, 8, and 9 are correct and the others are experiencing some crazy anomoly. For example Employee 6 rode a total of 4 times, 7:02 Hours, 5:46 Hours, 6:33 Hours, and 6:46 hours, however the calculation is only reading 2:07 minutes. This is the same situation for Employee 3. What is going on here. Any suggestions.

The Starting and ending DateTime's are derived from a table (Two separate fields). Hours is derived from a Query field using TotalHours: Format([EndDateTime]-[StartDateTime],"Short Time") and the "total Hours" is derived in the report with a command of =Sum([EndDateTime]-[StartDateTime])

 

[vbaInet]

Upload your db let me take a look.

 

[spikepl]

There is no anomaly, just confusion. Access DateTime deals with dates (=offsets from 30/12 1899) , not time intervals: http://support.microsoft.com/kb/210276

so your "anomalous" results are due to your asking for the Time (that will never exceed 23:59:59) of a sum of datetimes, and forgetting that there is a Date component too.