Using SUM with a short time format

  • Thread starter Thread starter Ant
  • Start date Start date
A

Ant

I have a calculated field in a continuous Sub form which is arrived at by
subtracting an end_time from a start_time to show the time worked in ‘Short
time’ format. I would now like to sum all the calculated time_worked fields
in the footer of the sub form to give me a total number of hours worked. I
have tried =Sum([time_worked]) and just get error with no hint as to why?
Am I overlooking something silly?



Thank for any help



Ant
 
Its just '#Error' ?

Liz said:
What is the error message?
-----Original Message-----
I have a calculated field in a continuous Sub form which is arrived at by
subtracting an end_time from a start_time to show the time worked in 'Short
time' format. I would now like to sum all the calculated time_worked fields
in the footer of the sub form to give me a total number of hours worked. I
have tried =Sum([time_worked]) and just get error with no hint as to why?
Am I overlooking something silly?



Thank for any help



Ant



.
Click to expand...
Click to expand...
 
I have a calculated field in a continuous Sub form which is arrived at by
subtracting an end_time from a start_time to show the time worked in ‘Short
time’ format. I would now like to sum all the calculated time_worked fields
in the footer of the sub form to give me a total number of hours worked. I
have tried =Sum([time_worked]) and just get error with no hint as to why?
Am I overlooking something silly?
Click to expand...

Well, it's not silly - but it is an oversight.

Access Date/Time fields are not really suitable for durations. They're
stored as a Double Float number, a count of days and fractions of a
day since midnight, December 30, 1899.

Rather than subtracting two Date/Times to get a duration, it's better
to use the DateDiff() function to get the elapsed time in miNutes:

Elapsed: DateDiff("n", [StartTime], [EndTime])

(note that "m" would get the time in Months).

This integer value can then be summed, averaged, etc. like a normal
number.

To display it in hours:minutes format use an expression like

[Duration] \ 60 & Format([Duration] MOD 60, ":00")
 
Looks like just the job.

Thanks for that I'll get right on the case now

John Vinson said:
I have a calculated field in a continuous Sub form which is arrived at by
subtracting an end_time from a start_time to show the time worked in 'Short
time' format. I would now like to sum all the calculated time_worked fields
in the footer of the sub form to give me a total number of hours worked. I
have tried =Sum([time_worked]) and just get error with no hint as to why?
Am I overlooking something silly?
Click to expand...

Well, it's not silly - but it is an oversight.

Access Date/Time fields are not really suitable for durations. They're
stored as a Double Float number, a count of days and fractions of a
day since midnight, December 30, 1899.

Rather than subtracting two Date/Times to get a duration, it's better
to use the DateDiff() function to get the elapsed time in miNutes:

Elapsed: DateDiff("n", [StartTime], [EndTime])

(note that "m" would get the time in Months).

This integer value can then be summed, averaged, etc. like a normal
number.

To display it in hours:minutes format use an expression like

[Duration] \ 60 & Format([Duration] MOD 60, ":00")
Click to expand...
 
Back
Top