Date Type Conversion Failure using DoCmd.TransferText 1

[AlisonH]

I'm trying to update tables from a CSV file when my db opens using:

DoCmd.TransferText acImportDelim, "MPAN", "MPAN Details", strFile, -1

The CSV file includes a couple of date fields that I've set as date/time in the spec file but I get a type conversion failure every time. The format in the CSV file is hh:mm dd/mm/yy and if I open it in Excel and format it first it works fine. To complicate it some of the records just hold 00:00 if there's no value in the system it's generated by.

Unfortunately I can't get the format of the CSV file changed and I need them as dates so I can use them in a query to calculate elapsed time. Has anyone got any suggestions?

Thanks, Alison.

 

[JoeMiller]

Try changing your spec file and specifying that the date field be pulled using the DateValue() function. DateValue examines a string and extracts the Date if possible. It should fix your problem. Joe Miller
joe.miller@flotech.net

 

[AlisonH]

Thanks for replying, Joe. It sounds just what I want but I can't work out how to do it - I've checked online help and I'm no wiser.

I set the spec file up just by going through Get External Data ... and saving it, and I can only select from the list of date types in there. Is there a way to write your own spec file so that you can edit it?

Yours gratefully, Alison.

 

[JoeMiller]

Rather than importing the data directly in, import it into a temporary table making sure that the suspect date field is imported as a Text field. Then once it's in your temp table, run an append query that formats the date field using the datevalue function like so:

Expr1: DateValue([MyDateField])

HTH Joe Miller
joe.miller@flotech.net

 

[AlisonH]

Got it - thanks.

Alison