Data type Conversion

[Jeff Fredel via AccessMonster.com]

I am importing a text file into an MS Access 200 db table. I have a field
that is supposed to be a date field. An eample of the data is 12May2004.
After I import the file I cannot change the data type from text to date. I
also can't change it in the import specs - it deletes the field or does not
import it. Any ideas?

 

[Douglas J. Steele]

You could add a new date field, run an update query to populate the new
field, then delete the old field once you know it's correct.

You could also try using ALTER TABLE [MyTable] ALTER COLUMN [MyField] DATE

 

[Jeff Fredel via AccessMonster.com]

Sorry, a lil slow tonight. Can you step me thru it? Thanks so much for
the assistance.

 

[Douglas J. Steele]

Since AccessMonster doesn't put history when you post, it's difficult for
others to follow, so I'll repost my original reply:

You could add a new date field, run an update query to populate
the new field, then delete the old field once you know it's correct.

What part are you having problems with?

Presumably you know how to add a field to an existing table, as well as how
to delete one (steps 1 and 3 above).

To create an update query, add the table to the query, drag the new field
you added to the grid, convert the query to an Update query (on the Query
menu) and put CDate([old field name]) in the "Update to" row under the
field.

The SQL for the query will look something like:

UPDATE MyTable SET MyDateField = CDate([MyIncorrectField])

 

[Jeff Fredel via AccessMonster.com]

Thank you for your time and assistance. I tried the update query as you
suggested. Unfortuneately it did not work nor update any records. i
received a "type conversion failure" error".

The current field name is "FST_SVC_DT" it is currently text type and its
data is in the format of "13May2005".

Any ideas on what Im doing wrong or other suggestions?

Thanks again for your help

 

[Douglas J. Steele]

What's the SQL of your update query look like? (If you're not familiar with
that, choose SQL View from the View menu when your query is open in design
mode, and copy all of the test into your reply)

 

[Joe Fallon]

You should import the data to a temp Access table.
Then write a query to parse the Text data and turn it into a real Date and
then use that query to append the data to your real table. This is the
"standard way" to handle imperfect data on import.

12May2004 would be parsed into DAY MONTH YEAR using Left, Mid and Right
functions.
Then combine them into a real date.