TXT file Import - Date / text field conversion

[Jeff Fredel via AccessMonster.com]

Thanks in advance for any assistance.

I have a csv that I am importing into an Access 2000 DB. I have a field
named "FST_SRVC_DT" that I need to be treated as a date type field. I can
not change the field type at import (returns blank fields) and I cannot
change the type in the design view of the table. The current format of the
field is "13May2005".

I tried an update query whereby I created a new date field called First
Date and used the following function in the "update to" section of my query
under the nedw field:

CDate([FST_SRVC_DT])

I also used the custom format of "ddmmmyyyy" in the table design format
field for the new one I created.

I still get a "type conversions failure" when I run the update query.

Any assistance would be greatly appreciated. I just dont know what Im
missing

 

[John Nurick]

Hi Jeff,

You need to give CDate() a format it recognises. Try something like
this:

CDate((Left([FST_SRVC_DT],2) & " " & Mid([FST_SRVC_DT], 3,
Len([FST_SRVC_DT]) - 6) & " " & Right([FST_SRVC_DT], 4)))

although you'll have to use something more complicated if the first of
the month is 1May2005 and not 01May2005.

Thanks in advance for any assistance.

I have a csv that I am importing into an Access 2000 DB. I have a field
named "FST_SRVC_DT" that I need to be treated as a date type field. I can
not change the field type at import (returns blank fields) and I cannot
change the type in the design view of the table. The current format of the
field is "13May2005".

I tried an update query whereby I created a new date field called First
Date and used the following function in the "update to" section of my query
under the nedw field:

CDate([FST_SRVC_DT])

I also used the custom format of "ddmmmyyyy" in the table design format
field for the new one I created.

I still get a "type conversions failure" when I run the update query.

Any assistance would be greatly appreciated. I just dont know what Im
missing