Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2004
    Posts
    173

    Answered: Convert Number to Time Value

    I have the following types of values:

    11
    2011
    92011
    192011

    They are numbers but they indicate time. The first value is 11 seconds after midnight 12:00:11, The second is 12:20:11, the third is 9:20:11 (AM), and the fourth is 7:20:11 (PM). How can I convert these types of values quickly in Access? They don't have a consistent number of digits. I've done this in excel in the past by separating the values into each group of 2 digits you'd expect to see in a time value using the int and mod functions. Is there an easier way that I'm overlooking?

    Thanks.

  2. Best Answer
    Posted by InsuranceGuy

    "
    Quote Originally Posted by mmbosman View Post
    I have the following types of values:

    11
    2011
    92011
    192011

    They are numbers but they indicate time. The first value is 11 seconds after midnight 12:00:11, The second is 12:20:11, the third is 9:20:11 (AM), and the fourth is 7:20:11 (PM). How can I convert these types of values quickly in Access? They don't have a consistent number of digits. I've done this in excel in the past by separating the values into each group of 2 digits you'd expect to see in a time value using the int and mod functions. Is there an easier way that I'm overlooking?

    Thanks.
    Try this:

    Code:
    timevalue(format(right("000000"&cstr([fieldname]),6),"00:00:00"))
    Jeff"


  3. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Write a function that convets the number inyo a datetime value.
    I'd rather be riding on the Tiger 800 or the Norton

  4. #3
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    Parse it into a date field

    = hr & ":" & ":"& min & ":" & sec

  5. #4
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,420
    Provided Answers: 7
    There more than on way to skin a cat

    base on what you asked

    Code:
    Sub TESTIT()
    Debug.Print TimeIt("11")
    Debug.Print TimeIt("2011")
    Debug.Print TimeIt("92011")
    Debug.Print TimeIt("192011")
    
    End Sub
    
    Function TimeIt(ThisTime)
    
    Select Case Len(ThisTime)
    
    Case 2
     TimeIt = "12:00:" & ThisTime
    Case 4
     TimeIt = "12:" & Mid(ThisTime, 1, 2) & ":" & Mid(ThisTime, 3, 2)
    Case 5
     TimeIt = Mid(ThisTime, 1, 1) & ":" & Mid(ThisTime, 2, 2) & ":" & Mid(ThisTime, 4, 2)
    Case 6
     TimeIt = Val(Mid(ThisTime, 1, 2)) - 12 & ":" & Mid(ThisTime, 3, 2) & ":" & Mid(ThisTime, 5, 2)
    
    End Select
    
    End Function
    then in a query

    realtime:timeit([feildnamethathasthedata])
    Last edited by myle; 08-24-15 at 15:51. Reason: spelling
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  6. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    If the time is 12 seconds AFTER midnight then presumably it should be 00:00:12 NOT 12:00:12?
    Myle's code does the job, as you would expect
    But fir me Id want to return a datetime value which could then be formatted as required in the data consumer / presentation layer, whilst retaining the ability to use date time functions for aggregation.

    As a refinement id suggest instead of looking for an exact length you look at 3 cases <=2, <=4 & <=6 You probably need to make the function more robust to handle values out of range eg 7 or more digits, no digits, alphanumerics, nulls etc...

    But thats polishing what already meets the OP's stated requirements
    Last edited by healdem; 08-25-15 at 06:02.
    I'd rather be riding on the Tiger 800 or the Norton

  7. #6
    Join Date
    Aug 2015
    Posts
    3
    Provided Answers: 1
    Quote Originally Posted by mmbosman View Post
    I have the following types of values:

    11
    2011
    92011
    192011

    They are numbers but they indicate time. The first value is 11 seconds after midnight 12:00:11, The second is 12:20:11, the third is 9:20:11 (AM), and the fourth is 7:20:11 (PM). How can I convert these types of values quickly in Access? They don't have a consistent number of digits. I've done this in excel in the past by separating the values into each group of 2 digits you'd expect to see in a time value using the int and mod functions. Is there an easier way that I'm overlooking?

    Thanks.
    Try this:

    Code:
    timevalue(format(right("000000"&cstr([fieldname]),6),"00:00:00"))
    Jeff

  8. #7
    Join Date
    Apr 2004
    Posts
    173
    Thanks so much for your help with the multiple answers. The timevalue(format(right("000000"&cstr([fieldname]),6),"00:00:00")) did the trick and was the easiest to implement. Thanks again.

  9. #8
    Join Date
    Aug 2015
    Posts
    3
    Provided Answers: 1
    Quote Originally Posted by mmbosman View Post
    Thanks so much for your help with the multiple answers. The timevalue(format(right("000000"&cstr([fieldname]),6),"00:00:00")) did the trick and was the easiest to implement. Thanks again.
    Thanks for the confirmation sir!

    Jeff

Posting Permissions

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