Convert SECONDS to HH:MM:SS in a QUERY

[kev100 via AccessMonster.com]

I've got a query that simply displays the contents of an employee activity
table.

The fields are:

-Name
-Login_Date
-Login_Time (displayed in seconds passed since midnight)
-On_Break (displayed in seconds passed since midnight)
-Off_Break (displayed in seconds passed since midnight)
-LogOut_Time (displayed in seconds passed since midnight)

When the query opens....it simply displays all employees sorted by NAME, then
LOGIN DATE

I'm needing to be able to add a column in the query results that displays
Login_Time and Log_Out Time as HH:MM:SS rather than just the total seconds
from midnight.

I think there is some soft of formula...but am not sure how to use it like
this in a query.

Thanks very much.

 

[John Spencer]

I would use the DateAdd function

DateAdd("s",0,[Login_Time])

An alternative is to use the following

CDate(Login_Time / 86400)

Watch out for null values. You might need to add a test for that

IIF(Login_Time is Null, Null, CDate(Login_Time/86400))


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[kev100 via AccessMonster.com]

John,

Thanks....that converts the seconds (total seconds passed since midnight) to
a Date.

Is there a similar function would convert it to a regular time (hh:mm:ss) ?

Thanks

I would use the DateAdd function

DateAdd("s",0,[Login_Time])

An alternative is to use the following

CDate(Login_Time / 86400)

Watch out for null values. You might need to add a test for that

IIF(Login_Time is Null, Null, CDate(Login_Time/86400))

I've got a query that simply displays the contents of an employee activity
table.

[quoted text clipped - 21 lines]

Thanks very much.

 

[kev100 via AccessMonster.com]

John,

I think I just found it....it is a variation of the above:

DateAdd("s", [Seconds], #12:00:00 AM#)


...this will convert total seconds elapsed since midnight of the CURRENT day
to hh:mm:ss AM/PM

Thanks very much,
Kev

 

[John Spencer]

Both returned a time for me.

Are you sure that Login_Time is the seconds since midnight?

For example

CDate(3600/86400) returned 01:00:00 for me.

If the total number of seconds exceeds 86400 then you will get a date
and time since time rolls over when you get more than 24 hours worth.
In that case you might try TimeValue

TimeValue(DateAdd("s",0,Login_Time))

or as an alternative the following (untested) should return just the
time portion.
CDate(Login_Time MOD 86400)

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

John,

Thanks....that converts the seconds (total seconds passed since midnight) to
a Date.

Is there a similar function would convert it to a regular time (hh:mm:ss) ?

Thanks

I would use the DateAdd function

DateAdd("s",0,[Login_Time])

An alternative is to use the following

CDate(Login_Time / 86400)

Watch out for null values. You might need to add a test for that

IIF(Login_Time is Null, Null, CDate(Login_Time/86400))

I've got a query that simply displays the contents of an employee activity
table.

[quoted text clipped - 21 lines]

Thanks very much.

 

[Chris2]

John,

I think I just found it....it is a variation of the above:

DateAdd("s", [Seconds], #12:00:00 AM#)


..this will convert total seconds elapsed since midnight of the CURRENT day
to hh:mm:ss AM/PM

Thanks very much,
Kev

kev100,

? DateAdd("s", 86401, #12:00:00 AM#)

That isn't quite doing what you want.

The above returns: 12/31/1899 12:00:01 AM

Not: 24:00:01

Try:

Public Function ElapsedTimeFromSeconds(InputSeconds As Long) As String

' Created: 2007-07-17.
' Purpose: Calculate an elapsed time from a total number
' of seconds.
' Returns: A string in the format of hh:mm:ss
' Errors: "-1", indicates InputSeconds < 0,
' which cannot happen.

Dim HoursFromSeconds As Long
Dim MinutesFromSeconds As Long
Dim SecondsRemaining As Long

If InputSeconds < 1 Then
ElapsedTimeFromSeconds = "-1"
Else
HoursFromSeconds = InputSeconds \ 3600
MinutesFromSeconds = (InputSeconds Mod 3600) \ 60
SecondsRemaining = (InputSeconds Mod 3600) Mod 60
ElapsedTimeFromSeconds = _
Format(HoursFromSeconds, "00") & ":" & _
Format(MinutesFromSeconds, "00") & ":" & _
Format(SecondsRemaining, "00")
End If

Exit_ElapsedTimeFromSeconds:

Exit Function

ErrorHandler:

' General error handling. This should probably be
' replaced by a central error handling function.

MsgBox "Error Description: " & Err.Description & vbCr & _
"Error Number: " & Err.Number, vbExclamation, _
"An Error Has Occurred in ElapsedTimeFromSeconds."

Resume Exit_ElapsedTimeFromSeconds:

End Function


The function call would appear as:

ElapsedTimeFromSeconds(86401)

Returning: 24:00:01


Sincerely,

Chris O.