ACC: Conversion Errors Importing Dates with Different Formats |
The information in this article applies to:
-
Microsoft Access versions 7.0, 97
SYMPTOMS
Moderate: Requires basic macro, coding, and interoperability skills.
When you import a fixed-width file with two Date fields with 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.
Microsoft Access 7.0 and 97 do not allow importing a Date field without
delimiters together with a Date field containing 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)
REFERENCES
For more information about update queries, search the Help Index for
"update queries," and then "change records as a group using an update
query."
For more information about importing, search the Help Index for
"Importing Data," or ask the Microsoft Access 97 Office Assistant.
Additional query words:
transfertext
Keywords : kberrmsg GnlFilem
Version : 7.0 97
Platform : WINDOWS
Issue type : kbprb