Converting Seconds (97 / SR2)

Topic

New Reply

I’ve been given a database that contains an integer field that is a seconds amount. I cannot find a way to convert it to hours:minutes:seconds. For example, to convert 463 to 00:07:43. It’s either that, or convert a text field containing 00:07:43 to a time format that I can then subtotal and total on. This data is being sent from a phone system to SQL, then to Access, so I guess there’s not too much flexibility…

Any suggestions are much appreciated!!

Kathy

Reply | Quote

Replies

I’d divide the # of seconds by 86,400, which is the number of seconds in a day, then stick the result in a date/time field. Then, you merely have to format this field as hh:nn:ss and you will have your answer.

Reply | Quote

>>Stick the result in a date/time field<<

I apologize, but I can't get it to work. I'm working in a query — CDate() isn't working. If I just use the Format([Value], "hh:nn:ss") it returns an #Error.

Thanks for your help!!

Kathy

Reply | Quote

Katty,
Put this code in a module.

Function intToTime(intSeconds As Integer) As Date
intToTime = Format(intSeconds / 86400, “hh:mm:ss”)
End Function

In the Control Source of your control put :
= intToTime(YourFieldWWithSeconds)

Francois

Reply | Quote

Francois,
Thank you for your help!! The code runs from my report, but instead of 463 (seconds) returning 00:07:43 (hours:minutes:seconds), it is returning 12:07:43 AM. — Do you have any ideas of what I’m doing wrong?

Kathy

Reply | Quote

Kathy,

This has to make with the time format of windows.
Try to change the line
Function intToTime(intSeconds As Integer) As Date
to
Function intToTime(intSeconds As Integer) As String

This should work (I hope )

Francois

Reply | Quote

Francois,
That prints out beautifully. Thank you — I greatly appreciate your help!! I learn so much on this board…

One last problem (oops, I mean “challenge”) I’m dealing with. After getting all the seconds to convert to time for the report, I have to subtotal and total them. I tried summing the seconds as an integer, then sending that calculated field’s results through the function you created. But, I’m receiving a #Num! error. From Microsoft’s KB article, I see that means “the value in the field is too large to be stored in the field, based on the field’s DataType or FieldSize property setting.” Since this is a text box on a report, I’m assuming the Date data type cannot handle that many hours in the result.

I have another piece of code from a KB article, but I’m getting an overflow error on it:

All variables are declared as Doubles
totalhours = Int(CSng(interval * 24))
totalminutes = Int(CDbl(interval * 1440))
totalseconds = Int(CDbl(interval * 86400))
hours = totalhours Mod 24
minutes = totalminutes Mod 60 –this is where the overflow error occurrs–
seconds = totalseconds Mod 60

Any suggestions are greatly appreciated!!

Thanks,
Kathy

Reply | Quote

Kathy,

Try a new version of the function

Function intToTime(lngSeconds As Long) As Date
intToTime = Format(lngSeconds / 86400, “Hh:mm:ss”)
End Function

This will work for a maximum of 23 h 59 min. Above it will give bad results. If you need more, let me know and I look for something else (tomorrow, it’s 11pm here).

Francois,
Always ready for new challanges

Reply | Quote

Kathy,

The following function converts your seconds in time format even if you are above 23:59:59
It is also completely independent of the time format settings of windows

Function lngToTime(lngSeconds As Single) As String
Dim lngHH As Long
Dim lngMM As Long
Dim lngSS As Long
lngHH = Int(lngSeconds / 3600)
lngMM = Int((lngSeconds – 3600 * lngHH) / 60)
lngSS = lngSeconds – (3600 * lngHH) – (lngMM * 60)
lngToTime = CStr(lngHH) & “:” & Format(CStr(lngMM), “00”) & “:” & Format(CStr(lngSS), “00”)
End Function

Francois

Reply | Quote

Francois,
I can tell you how much your help as been — my totals were going above 23:59:59. I really appreciate the time you spent in helping me. My report is now working! I haven’t been this happy over a completed project in a long time… And, this function has already been filed away in my Code Librarian!!

Thanks again, and have a great week!

Kathy

Reply | Quote

Try this:

format( Cdate(Value/86400),”hh:nn:ss”)

Reply | Quote

Mark,
It works like a charm — this board is a lifesaver!!

Thanks,
Kathy

Reply | Quote