Microsoft KB Archive/296572

= ACC2002: Conversion Errors When You Import Dates That Have Different Formats =

Article ID: 296572

Article Last Modified on 11/5/2003

-

APPLIES TO


 * Microsoft Access 2002 Standard Edition

-



This article was previously published under Q296572



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

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

For a Microsoft Access 2000 version of this article, see 208591.



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

Finished importing file to table. Not all your date was successfully imported. Error description with associated row numbers of bad records can be found in the Microsoft Access table ' _ImportErrors'.



CAUSE
You may receive this error message when one of the Date fields 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 you must 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 that contains 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) & &quot;/&quot; & Mid$([Mydate],3,2) & &quot;/&quot; & Right$([Mydate],2)

