Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2009
    Posts
    46

    Unanswered: Need Help Converting Text Data to Date Format

    Hey guys,

    I'm a noob here so please excuse my ignorant. I have a simple Access database which receives a text file import. I set all the fields as text data type, but I need the date data to be reformatted.

    In the import file, the date looks like this for a field called INS_DATE:

    09252012

    I want a mm/dd/yy format. I tried changing the datatype in the field to this format DATETIME, but Access told me that it would have to erase all the data in the field. So I emptied the table and tried a fresh import. Now I get data conversion errors.

    Ok, fine, I guess I can run an update query to change the data, but I'm stuck on this too. I tried using an expression with the CDATE function, but that gave me errors.

    Is there a simple way to do this?

    Thanks!

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    store the data as date/time datatype. if that measn you need to run another process on the import do so
    ..import the data into a temporary table
    move the data from that temporary table and convert datatypes as required
    you can convert a string value to a date format that can be inserted into another table using
    left$(mycolumn,2) & "\" & mid$(mycolumn,3,2) & "\" & right(mycolumn,4)
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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