ACC2002: Conversion Errors When You Import Dates That Have Different Formats (296572)

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 <path> to table <name>. Not all your date was successfully imported. Error description with associated row numbers of bad records can be found in the Microsoft Access table '<name>_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) & "/" & Mid$([Mydate],3,2) & "/" & Right$([Mydate],2)

REFERENCES

For more information about update queries, click Microsoft Access Help on the Help menu, type create an update query in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

For more information about importing data, click Microsoft Access Help on the Help menu, type importing data in the Office Assistant or the Answer Wizard, and then click Search to view the topic.