Problem with time?

[souprog]

I'm summing a field in a query that is a short time format. The problem is that when then field sums to more than 24 hrs it rolls back to 0:00 and continues adding.

For example, the if the result I should get is 38:30, it gives me 14:30 (ie. 38:30 - 24:00). How can I stop this from clicking over to the next day. I need the hours returned to be above 24 to calculate the hours worked in a week.

Any thoughts?

cheers
Souprog

 

[MichaelRed]

Hmmmmmmmmmm,

ALL MS date/time fields are both date AND time (stored internally as a DOUBLE). So the actual "SUM" should not be a problem. However, the FORMAT of the field as short time DOES limit the display to the fractional part, so your results need to be formatted as something else -all over again.

I created the following small sample:

The Table:
MyIndex RaceTime
1 1:00
2 1:22
3 3:26
4 2:12
5 1:06
6 1:23
7 2:56
8 6:25
9 8:15
10 6:45
11 9:15
12 5:30
13 7:15


The Query:
SELECT Sum(RaceTime_2.RaceTime) AS MyTime, 24*[MyTime] AS Hours
FROM RaceTime_2;

The results:
MyTime Hours
2.3702662037037 56.8863888888889

If you look at the SQL (the Query), you will see that the time is simply the aggregate function of the individual (Short Time) values, while the query results (UNFORMATTED) is just a floatiing point (DOUBLE) value. The Integer ("2&quot part reresents the total number of DAYS accumulated, whilr the fractional part ".370266..." represents the partial additional day. The Hours just calculates the "Hours" in the total accumulated in "MyTime". In a real world situation, there would be an ID field to group on, but the remainder should be more or less the same.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[souprog]

Thanks mate.... That explains that weird decimal number that I was originally getting! Multiply it by 24 and it all makes sense....