09-25-12, 14:03 #1Registered User
- Join Date
- Jan 2009
Unanswered: Need Help Converting Text Data to Date Format
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:
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?
09-25-12, 14:27 #2Jaded Developer
Provided Answers: 59
- Join Date
- Nov 2004
- out on a limb
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