Calculate total time for report

[AbeA]

this should be an easy one. Not sure why I can't figure this out.

I have one table, Leopard Test Scores 2007, that has only two fields, employee and total. The total column has a "Date/Time" data type with a short time format. All entries in the total are in the "mm:ss" format, e.g. 03:23, 12:39, so on.

All I need to do is calculate the total minutes for ALL records in this table for a report. If the amount of minutes is over 60 (it isnt now but will be in the future), I want it to be able to list 1 hour 15 minutes rather than 75 minutes in the report. If I add a third column in the query with the time field, then set the totals to "sum" it does not look right in the report (it comes out like .01278).

Thanks!

 

[KenHigg]

Can you use the FormatDateTime() function?

 

[AbeA]

I'm trying to enter the FormatDateTime code in my query but it keeps putting the vbShortTime in brackets: TotalTime: FormatDateTime([Time],[vbShortTime])

 

[boblarson]

It needs to be in quotation marks and for this spot it is just "ShortTime" instead of vbShortTime.

 

[KenHigg]

Or I think you can simply put:

TotalTime: FormatDateTime([Time],"4")


ken

 

[AbeA]

Thanks Ken and Bob. Ken, I put in your code and it only lists the first "time" field (not the total of all fields) in the report.

e.g. the report lists all time fields "3:20, 3:40, 5:40, etc) when I add the totaltime field to the report it only lists the first one, (3:20).. If I set the total to "sum" for the totaltime field in the query, I get a data type mismatch.

what should I do next?