Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Apr 2004
    Location
    Jacksonville, Florida, USA
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I've got an Access query that shows EVENT_END and DURATION. To get EVENT_START you subtract DURATION from EVENT_END. Problem is, the way the db is setup, EVENT_END is a number data type, and DURATION, which is in seconds, is a text data type. I'm trying to end up with EVENT_START in hhmmss. I think I need to get the data types similar first, but I'm not sure how to get the hhmmss format in the query result. Any help will be appreciated. Thanks.

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,435
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Hey Dad,

    Try using the TimeSerial() function. You'll have to convert the Text data to a number first though.
    If you over load one of the arguments it will automatically convert to the next largest unit, e.g. if you give it 65 seconds it will convert it into 1:05 (one minute & 5 seconds).

    Just enter TimeSerial into the VBA help box for the information.

    Good Luck.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    2 Star Lounger
    Join Date
    Apr 2004
    Location
    Jacksonville, Florida, USA
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I got DURATION into hhmmss, but the way EVENT_END is numbered is causing another problem. The db it comes from gives me a number (data type is number) like "84631" to indicate 08:46:31AM. Now I can make it look like time by simply inserting the colons, but it's not actual time, so I can't subtract DURATION from it. How do I convert this number string into time that Access will recognize as time? Thanks.


  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,435
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Dad,

    This shows how to do it. You can integrate it into your code.

    Code:
    Function NumberToTime(lMyNum As Long) As Boolean
    
       Dim iSecs As Integer
       Dim iMins As Integer
       Dim iHrs  As Integer
       Dim dtTime As Date
       
       
       iSecs = lMyNum Mod 100 ' Returns Seconds
       
       iMins = ((lMyNum \ 100) Mod 100)  'Returns Minutes
       
       iHrs = (lMyNum \ 10000) 'Returns Hours
       
       
       MsgBox "Your number contains:" & vbCrLf & _
              Format(iSecs, "##") & " Seconds" & vbCrLf & _
              Format(iMins, "##") & " Minutes" & vbCrLf & _
              Format(iHrs, "##") & " Hours", vbOKOnly, _
              "Conversion of Number to Time"
       
      'Convert to TimeValue
      
       dtTime = TimeValue(Format(iHrs, "##") & ":" & _
                          Format(iMins, "##") & ":" & _
                          Format(iSecs, "##"))
       MsgBox "DateTime Value is: " & Format(dtTime, "hh:mm:ss"), _
               vbOKOnly, "Shown as Time"
    End Function
    This is a standalone function so you can copy it into the VBE and call it from the Immediate window to test it.

    I hope this solves your problem.
    Attached Images Attached Images
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    2 Star Lounger
    Join Date
    Apr 2004
    Location
    Jacksonville, Florida, USA
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts
    If possible, I'd like to do this conversion in the query field box, since there are currently no code modules running in this db. Is that possible, or do I need to add a function to do this?


  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,435
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Quote Originally Posted by merlynsdad View Post
    If possible, I'd like to do this conversion in the query field box, since there are currently no code modules running in this db. Is that possible, or do I need to add a function to do this?
    To the best of my knowledge you will have to add a function. Sorry.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    Quote Originally Posted by merlynsdad View Post
    If possible, I'd like to do this conversion in the query field box, since there are currently no code modules running in this db. Is that possible, or do I need to add a function to do this?
    Why not use TimeSerial in a query?

    Can you download a sample database so we can see if it can be done inside a query.

  8. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,435
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Patt,

    Good Idea. See graphics.

    Dad - I hope this solves your problem.
    Attached Images Attached Images
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

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