Hours calculation problem

B

Billy B

I have the following query that is supposed to show total hours but the hours
show up as 09:187 in the Total Hours: [THrs] & ":" & [TMin] part of the query.

How can I fix it?

Thank you.
 
B

Billy B

Sorry I forgot to post the SQL statement...

SELECT DISTINCTROW Year(Statistics.StatDate) AS [Year],
Sum(Statistics.Mileage) AS [Timed Miles], Sum(Statistics.[Total Miles]) AS
[Total Miles], Sum(Statistics.ESeconds) AS SumOfESeconds,
Sum(Statistics.TSeconds) AS SumOfTSeconds, Format(Sum([TSeconds]\3600),"00")
AS THrs, Format(Sum([TSeconds] Mod 3600\60),"00") AS TMin, [THrs] & ":" &
[TMin] AS [Total Hours]
FROM Statistics
GROUP BY Year(Statistics.StatDate)
ORDER BY Year(Statistics.StatDate) DESC;
 
R

Rob Parker

I suspect the problem is in the

Format(Sum([TSeconds] Mod 3600\60),"00") AS TMin

clause. This will probalby be giving you [TSeconds] Mod 60. Try:

Format(Sum(([TSeconds] Mod 3600)\60),"00") AS TMin

HTH,

Rob

Billy B said:
Sorry I forgot to post the SQL statement...

SELECT DISTINCTROW Year(Statistics.StatDate) AS [Year],
Sum(Statistics.Mileage) AS [Timed Miles], Sum(Statistics.[Total Miles]) AS
[Total Miles], Sum(Statistics.ESeconds) AS SumOfESeconds,
Sum(Statistics.TSeconds) AS SumOfTSeconds,
Format(Sum([TSeconds]\3600),"00")
AS THrs, Format(Sum([TSeconds] Mod 3600\60),"00") AS TMin, [THrs] & ":" &
[TMin] AS [Total Hours]
FROM Statistics
GROUP BY Year(Statistics.StatDate)
ORDER BY Year(Statistics.StatDate) DESC;




Billy B said:
I have the following query that is supposed to show total hours but the
hours
show up as 09:187 in the Total Hours: [THrs] & ":" & [TMin] part of the
query.

How can I fix it?

Thank you.
Click to expand...
Click to expand...
 
B

Billy B

Rob,
I tried it and it still did not work. My db contains a field with seconds
only and I would like that converted to HH:MM:SS as the result.

Rob Parker said:
I suspect the problem is in the

Format(Sum([TSeconds] Mod 3600\60),"00") AS TMin

clause. This will probalby be giving you [TSeconds] Mod 60. Try:

Format(Sum(([TSeconds] Mod 3600)\60),"00") AS TMin

HTH,

Rob

Billy B said:
Sorry I forgot to post the SQL statement...

SELECT DISTINCTROW Year(Statistics.StatDate) AS [Year],
Sum(Statistics.Mileage) AS [Timed Miles], Sum(Statistics.[Total Miles]) AS
[Total Miles], Sum(Statistics.ESeconds) AS SumOfESeconds,
Sum(Statistics.TSeconds) AS SumOfTSeconds,
Format(Sum([TSeconds]\3600),"00")
AS THrs, Format(Sum([TSeconds] Mod 3600\60),"00") AS TMin, [THrs] & ":" &
[TMin] AS [Total Hours]
FROM Statistics
GROUP BY Year(Statistics.StatDate)
ORDER BY Year(Statistics.StatDate) DESC;




Billy B said:
I have the following query that is supposed to show total hours but the
hours
show up as 09:187 in the Total Hours: [THrs] & ":" & [TMin] part of the
query.

How can I fix it?

Thank you.
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

First Sum all the seconds
Then divide by 60 to get the number of minutes
Then use the Mod operator to get the number of minutes from 0 to 59 and
remove the hours
Then apply the formatting
1) Sum(TSeconds)
2) Sum(TSeconds) / 60
3) (Sum(TSeconds) / 60) Mod 60
4) Format((Sum(TSeconds) / 60) Mod 60,"00")

Format((Sum(TSeconds) / 60) MOD 60,"00") as TMin

This should round the minutes to the nearest minute

If you wanted to get whole minutes, then change to integer division

Format((Sum(TSeconds) \ 60) MOD 60,"00") as TMin

Rob Parker said:
I suspect the problem is in the

Format(Sum([TSeconds] Mod 3600\60),"00") AS TMin

clause. This will probalby be giving you [TSeconds] Mod 60. Try:

Format(Sum(([TSeconds] Mod 3600)\60),"00") AS TMin

HTH,

Rob

Billy B said:
Sorry I forgot to post the SQL statement...

SELECT DISTINCTROW Year(Statistics.StatDate) AS [Year],
Sum(Statistics.Mileage) AS [Timed Miles], Sum(Statistics.[Total Miles])
AS
[Total Miles], Sum(Statistics.ESeconds) AS SumOfESeconds,
Sum(Statistics.TSeconds) AS SumOfTSeconds,
Format(Sum([TSeconds]\3600),"00")
AS THrs, Format(Sum([TSeconds] Mod 3600\60),"00") AS TMin, [THrs] & ":" &
[TMin] AS [Total Hours]
FROM Statistics
GROUP BY Year(Statistics.StatDate)
ORDER BY Year(Statistics.StatDate) DESC;




Billy B said:
I have the following query that is supposed to show total hours but the
hours
show up as 09:187 in the Total Hours: [THrs] & ":" & [TMin] part of the
query.

How can I fix it?

Thank you.
Click to expand...
Click to expand...
Click to expand...
 
B

Bob Miller

I do a lot of time calculations in databases I design. I have found
that if I build a funtion for a calculation I use more than once it
make life easier. This function should do what you want, almost. It
doesn't put the leading 0 in hours les than 10 but you can probably
figure out how to do that in the code. You would place in one field of
the query Total Hours:Tim(TSseconds).

I created this about eight years ago in A97 as a novice and it probably
not very sophisticated but it keeps on ticking to this day.

Function Tim(SecTime As Single) As String
Dim hours, minutes As Integer
Dim Min$, DecTime
DecTime = SecTime / 3600
hours = Str(Int(Val(DecTime)))
minutes = Str((Val(DecTime) - hours) * 60)
If minutes > 0 Then
If minutes > 10 Then
Min$ = ":" + Right(Str(Int(minutes)), 2)
Else
Min$ = ":0" + Right(Str(Int(minutes)), 1)
End If
Else
Min$ = ":00"
End If
Tim = hours + Min$
End Function

Billy said:
Sorry I forgot to post the SQL statement...

SELECT DISTINCTROW Year(Statistics.StatDate) AS [Year],
Sum(Statistics.Mileage) AS [Timed Miles], Sum(Statistics.[Total Miles])
AS
[Total Miles], Sum(Statistics.ESeconds) AS SumOfESeconds,
Sum(Statistics.TSeconds) AS SumOfTSeconds,
Format(Sum([TSeconds]\3600),"00")
AS THrs, Format(Sum([TSeconds] Mod 3600\60),"00") AS TMin, [THrs] & ":"
&
[TMin] AS [Total Hours]
FROM Statistics
GROUP BY Year(Statistics.StatDate)
ORDER BY Year(Statistics.StatDate) DESC;




:

I have the following query that is supposed to show total hours but
the hours
show up as 09:187 in the Total Hours: [THrs] & ":" & [TMin] part of
the query.

How can I fix it?

Thank you.
Click to expand...
 
Top