ACC: Conversion Errors Importing Dates with Different Formats (142184)
The information in this article applies to:
- Microsoft Access for Windows 95 7.0
- Microsoft Access 97
This article was previously published under Q142184
Moderate: Requires basic macro, coding, and interoperability skills.
SYMPTOMS
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.
Modification Type: | Major | Last Reviewed: | 10/30/2003 |
---|
Keywords: | kberrmsg kbprb KB142184 |
---|
|