Sum of Time

[gmazza76]

Good afternoon,

I am summing time in a query in Access and seem to be having issues.
I am creating the report and summing the field where time is shown as "01:01:00" (1 hour, 1 minute & 0 seconds) the calculation in access shows it as a decimal number which seems to be fine.

I have the output table linked to Excel and this is where the error seems to occur as I cannot convert the information back to "hh:mm:ss" as it doesn't convert back accurately.

Is there anyway I can do this direct in access?

thanks in advance
Gavin

 

[welshgasman]

Have you tried the Format() function?

 

[gmazza76]

I have tried the below and I get an error "Data type missmatch in criteria expression" and the last one works but returns "00:00:00" across all fields

Sum(Format([time],"hh:nn:ss")) 

Format(Sum([Time]/86400),"hh:nn:ss")

Am I missing something in the way I am writing this?

thanks

 

[Micron]

If your field is really named Time, I'd say bad idea. Is your data datetime data type or is it text?
You might need DSum, not Sum.
Format function converts data to string type, so that could be the reason for that error.

 

[gmazza76]

The field is called "Hold Time" and I'm unsure what type it is as its via a linked table. I will have a look and see what DSum is and see what the difference is. Its weird as it will Sum the field in the query as a number, but it doesn't convert to time when I look at it in excel

 

[gmazza76]

Good morning all,

I have just checked my source data that is showing as a Date/Time format in another table. It seems as though the calculation is changing in the output of my query, is this standard or whats the best way to show the time as I import it into Excel.

I have tried this formula in my query, but it returns the values as "00:00:00".

Sum([Hold Time]) \ 3600 & Format(Sum([Hold Time]) \ 60 MOD 60, ":00") & Format(Sum([Hold Time]) MOD 60, ":00")

thanks

 

[Micron]

it will Sum the field in the query as a number, but it doesn't convert to time when I look at it in excel

That suggests the issue is with Excel cell formatting.
As for your expression, start small, like Sum([Hold Time]) and see what you're dividing by 3600. Add steps and see what you get.