Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Unanswered: Advanced Settings Date Format

    This is my text imported date format: 2007-07-27T00:00:00

    I select Advanced and change the format to YMD.

    However when I click finish an error oocurs and the date data is in the problems table.

    Is it because there is no Time delimiter (?

    How can I make this work on the import side?

    Thanks . . . Rick

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I reckon the "T" is causing the problems!
    How are you importing the date?
    George
    Home | Blog

  3. #3
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5

    just an idea

    you could import into straight text field and parse out the date and time using mid, left , and right functions and then update to correct date/time field

    if the time is unimportant it is alot easier but even with the time there you na do it in about 5 - 8 lines of code
    Dale Houston, TX

  4. #4
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5

    you could do it this way

    Public Sub getmydate()

    Dim mydate As String
    mydate = "2007-07-27T00:00:00" ' or a fieldname as well
    Debug.Print left(mydate, 10)
    ' this will return the value 2007-07-27

    End Sub
    with that parsed you could then use the right function to get the
    right string from the len(fieldname) - 11
    and that is the time value

    the mid would also work
    Dale Houston, TX

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    If you're going down the route of parsing the date (I don't recommend this unless it's a last resort - please answer post #2 first!)...
    But here are two slight modifications on axsprog's suggestion
    Code:
    Dim mydate As String
    mydate = "2007-07-27T00:00:00"
    
    MsgBox CDate(Left(mydate, InStr(1, mydate, "T") - 1))
    MsgBox CDate(Replace(mydate, "T", " "))
    Personally I prefer the second of the two.
    George
    Home | Blog

  6. #6
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5

    excellent modifications

    I never use the replace function - I am too old school and too code heavy. Which is not a good thing at times

    you arer right - it is a shorter way
    Dale Houston, TX

Posting Permissions

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