Microsoft KB Archive/208591

= ACC2000: Conversion Errors Importing Dates with Different Formats =

Article ID: 208591

Article Last Modified on 7/16/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q208591



Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access database (.mdb).

For a Microsoft Access 2002 version of this article, see 296572.



SYMPTOMS
When you import a fixed-width file with two Date fields that have different formats, individually you can import them, but if you try to import them together, you may receive the following error message:

Type Conversion Error



CAUSE
You may receive this error message when one Date field has no delimiters and the other Date field is formatted as a standard Date/Time field. Access does not allow you to import a Date field that does not have delimiters together with a Date field that does have delimiters. You must import the non-delimited Date field as Text, and then convert the data to a standard Date format.



RESOLUTION
If you want to import a file that contains data formatted in this manner, you must either edit the fixed-width field to add the slash mark (/) delimiter or import the field as Text, and then use an update query to convert the Text to a Date format.

For example, you can use the following expression in an update query to convert a Text field containing 010196 to a Date format of 01/01/96. You can then change the field in table Design view from a Text field to a Date field.

Left$([Mydate],2) & "/" & Mid$([Mydate],3,2) & "/" & Right$([Mydate],2)

