Calculating time using sum formula in access

[oggsta]

I am working in Access

And i would like to calculate the total number of hours in the feild [Time], the property is short time.

The formula i am using is as follows; =Sum([Time])

The problem is that once the calculation exceeds 24 hours it then reverts back to 0, for instance if the sum total was 26:00, the formula would display as 2:00.

Also i would like to calculate the total time where the feild date equals todays date, however not sure on the correct formula. The formula below does not work.

=Sum([Time]) and [date]=Date()

Thanks any advice appreciated.





Today()

 

[TonyJollans]

Hi oggsta,

I'm going to go digging and see if I can find a FAQ on dates and times and if there isn't one I'm going to write one. But meanwhile ...

Date/Time datatypes are held internally as numbers of days and all built-in functions treat them as dates and times offset from a base (not as durations). When you format them as times only the fractional part is considered so you never see a time more than 23:59:59. Arithmetic on them generally works as you would expect but some care is needed with negative values.

There are several ways of formatting the internal values as times like "26:00", all of which involve manipulation of the internal format number. In your case, one way would be ..

=Int(CDbl(Sum([Date]))*24) & ":" & Format(Sum([Date]),"nn")

As for the second part of your post, where are you trying to do this? In a Query? Can you post a few more details please.

Enjoy,
Tony

 

[oggsta]

Thanks for the reply sorry for the late reply i have been away;

The following formula works well
=Int(CDbl(Sum([Date]))*24) & ":" & Format(Sum([Date]),"nn&quot

As for the second part i did not even think of a query, i was thinking of a function statement as above.

If i were to use a query I want to select todays date and then claculate the sum of field [time] from teh query.

I guess the control would be (Queryname) Sum of [time]