DateDiff in Query

[Drum2001]

I am running a query with the following SQL below:

SELECT TimeClockHistory.ClockInDate, TimeClockHistory.ClockOutDate,
TimeClockHistory.Employee, Sum(DateDiff("n",[ClockIn],[ClockOut]))/60
AS TimeElapsed
FROM TimeClockHistory
GROUP BY TimeClockHistory.ClockInDate, TimeClockHistory.ClockOutDate,
TimeClockHistory.Employee;

ClockInDate, ClockOutDate, ClockIn, & ClockOut are all Date/Time
format.

I am trying to calculate the hours per week an employee works. Right
now when it is run, it might say... 8.31125455654 hours worked.

Is there any way, within the query I can make it so it will format
08:12 hours worked... As far as time is goes, a decimal format will not
work.

Any help would be greatly appreciated!

[Rich P]

To get a custom format from 8.31125455654 you will have to write some
code. Here is a little bit to help you get started:

Dim i As Single, j As Single, str1 As String
i = 8.31125455654
'--separate out the decimal portion of the number
'--and get the minutes
j = (i - Int(i)) * 60
If Int(i) < 10 Then
str1 = "0" & Int(i)
Else
str1 = Int(i)
End If
If Int(j) < 10 Then
str1 = str1 & ":0" & Int(j)
Else
str1 = str1 & ":" & Int(j)
End If

This will yield "08:18". Then you can append "hours worked"

HTH

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Drum2001]

Thank you!

[rquintal]

Here is a function I wrote, that converts a number of seconds to a
time.

USAGE:
sec2dur(DateDiff("s",[starttime],IIf(IsNull([stoptime]),Now(),[stoptime])))
Just modify to remove the & format(secs,"00") to display hhh:mm without
seconds.

Public Function sec2dur(seconds As Long) As String
'converts a start and stop time to number of seconds. and
'then displays as hhh:mm:ss

On Error Resume Next

Dim hrs As Long
Dim mins As Integer
Dim secs As Integer

hrs = Int(seconds / 3600)
mins = Int((seconds - (3600 * hrs)) / 60)
secs = seconds - (hrs * 3600 + mins * 60)

sec2dur = Format(hrs, "#,##0") & ":" & Format(mins, "00") & ":" &
Format(secs, "00")

End Function