Results 1 to 12 of 12
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Location
    Bloomington, Indiana, USA
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Converting Seconds (97 / SR2)

    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

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Converting Seconds (97 / SR2)

    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.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    Star Lounger
    Join Date
    Jan 2001
    Location
    Bloomington, Indiana, USA
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Converting Seconds (97 / SR2)

    >>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

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Converting Seconds (97 / SR2)

    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
    Francois

  5. #5
    Star Lounger
    Join Date
    Jan 2001
    Location
    Bloomington, Indiana, USA
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Converting Seconds (97 / SR2)

    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

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Converting Seconds (97 / SR2)

    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 <img src=/S/smile.gif border=0 alt=smile width=15 height=15>)

    Francois
    Francois

  7. #7
    Star Lounger
    Join Date
    Jan 2001
    Location
    Bloomington, Indiana, USA
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Converting Seconds (97 / SR2)

    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.
    <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>

    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

  8. #8
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Converting Seconds (97 / SR2)

    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
    Francois

  9. #9
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Converting Seconds (97 / SR2)

    Try this:

    format( Cdate(Value/86400),"hh:nn:ss")
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  10. #10
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Converting Seconds (97 / SR2)

    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
    Francois

  11. #11
    Star Lounger
    Join Date
    Jan 2001
    Location
    Bloomington, Indiana, USA
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Converting Seconds (97 / SR2)

    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

  12. #12
    Star Lounger
    Join Date
    Jan 2001
    Location
    Bloomington, Indiana, USA
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Converting Seconds (97 / SR2)

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

    Thanks,
    Kathy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •