Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    Toronto, Canada
    Posts
    101
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formatting a column (XP)

    I import data from Excel into Access and had a lot of clean up to do. I have a column that has a format of Feb17 04 and I need to format this to be a date and time. If I choose date and time for this column it deletes everything in the datebase. I was thinking I would have to place a space after the month and the day. If I am right is there a way I can do a find and replace to correct this?

    If not do anyone else have any suggestions

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formatting a column (XP)

    You mention "insert a space after the month and the day". In the example you posted, there is already a space between 17 and 04. What is the exact format of the values?

  3. #3
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Formatting a column (XP)

    My recommendation is, either clean up data in Excel (would not be hard), or as alternative, instead of importing directly into Access, link Excel file, then "import" data into Access with an append query. The append query can use an expression to "clean up" the bogus dates. Example SQL:

    INSERT INTO Table3 ( Field1 )
    SELECT CDate(Mid$([F1],4,2) & "-" & Left$([F1],3) & "-" & Right$([F1],2)) AS F1_DATE
    FROM ImportData;

    In example above, Table3 is destination table, where Field1 is Date/Time field. ImportData is name of linked Excel table (represents a named range in workbook); F1 is the field (column) with the bogus dates. The example assumes all data in the Excel field is formatted exactly as per the example you cited (ie, "Feb17 04"). In test, all bogus records were successfully appended to Table3 as valid date/time values.

    HTH

Posting Permissions

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