Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2003
    Posts
    64

    Unanswered: Text delimited numbers:

    Currently my files are being imported over using ODBC. With dates like, 112603 since these are text I have to convert this in order for Access to interpret the numbers like so,
    Date: CDate (Left ([myfield], 2) & "/" & Mid ([myfield], 3,2) & "/" & Right ([myfield], 2)), with the results like 11/26/03. Now the file is coming over like 11262003220927. How can I modify the expression to include both date and time? Like 11/26/2003 22:09:27.

    thanks

  2. #2
    Join Date
    Nov 2003
    Posts
    1,487
    Check this out...

    If MyField contains 112603

    MsgBox CDate(Left([MyField], 2) & "/" & Mid([MyField], 3, 2) & "/" & Right([MyField], 2)) & Format(Time, "h:m:s")

    Result: 11/26/200312:24:08

    Not what your looking for but what the heck...below is what you want

    Dim Strg1 As String, Strg2 As String
    Strg1 = CDate(Left([MyField], 2) & "/" & Mid([MyField], 3, 2) & "/" & Mid$(MyField, 5, 4))
    Strg2 = Mid$(MyField, 9,2) & ":" & Mid$(MyField, 11, 2) & ":" & Mid$(MyField, 13, 2)
    Strg1 = Strg1 & " " & Strg2
    MsgBox Strg1

    Placing the The actual date and time into seperate strings allows you the flexability to format either date or time to whatever format you like.
    As it stands now, Strg1 will display

    11/26/2003 22:09:27

    If you were to change line 3 to: Strg1 = Format(Strg1, "dd-mmm-yy") & " " & Format(Strg2, "Long Time")
    the result will be:

    26-Nov-03 10:09:27 PM

    Check out the Format function for many more formating possibilities.

Posting Permissions

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