Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2004
    Posts
    6

    Unanswered: Split text string

    Hi,

    I faced the problem of compare record based on the date.

    My date in raw database is in a text format, eg:"dd/mm/yyyy, time"
    But i need to import this data into another database. In this database, the date is in short date format, eg:"mm/dd/yyyy".

    I try a lot of ways to format the date bt i didnt get the correct result. I think of split the raw data into 3 group of numbers, eg: date, month, year. Time will be ignore. If anyone can tell me how to spit the date into 3 group of numbers?

    Please help me as i need this urgently....

    Thanks a lot.....

  2. #2
    Join Date
    Feb 2004
    Posts
    137
    Try this function:
    Code:
    Public Function dateOut(dateIn As String) As Date
    ' The following assumes date format "dd/mm/yyyy"
    ' in the first part of dateIn
    
        dateOut = DateSerial(CInt(Mid(dateIn, 7, 4)), _
            CInt(Mid(dateIn, 4, 2)), CInt(Mid(dateIn, 1, 2)))
    
    End Function
    ?dateOut("27/07/2004, 1252")
    ...should return:
    7/27/2004

  3. #3
    Join Date
    Jul 2004
    Posts
    6
    9/12/2003 2:44:40 PM

    This is my raw data.
    I trying ur code and return me "Type Mismatch" error.
    How to solve it?

  4. #4
    Join Date
    Feb 2004
    Posts
    137
    You stated previously that your raw data is in "dd/mm/yyyy, time" format.

    NOW, you're posting an example that's in d/m/yyy format. MAKE UP YOUR MIND!

    Try this, then:
    Code:
    Public Function dateOut(dateIn As String) As Date
    ' The following assumes date format "d/m/yyyy"
    ' in the first part of dateIn
    
        dateOut = DateSerial(CInt(Mid(dateIn, InStr(InStr(1, dateIn, "/") + 1, dateIn, "/") + 1)), _
            CInt(Mid(dateIn, InStr(1, dateIn, "/") + 1, _
            InStr(InStr(1, dateIn, "/") + 1, dateIn, "/") - InStr(1, dateIn, "/") - 1)), _
            CInt(Left(dateIn, InStr(1, dateIn, "/") - 1)))
    
    End Function

  5. #5
    Join Date
    Jul 2004
    Posts
    6
    It still return me "Type Mismatch" error...

Posting Permissions

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