ACC: Cannot Import YYMMDD Dates in Fixed-Width Text Files (93727)
The information in this article applies to:
- Microsoft Access 1.0
- Microsoft Access 1.1
This article was previously published under Q93727 Novice: Requires knowledge of the user interface on single-user computers.
SYMPTOMS
Microsoft Access will not import fixed-width text files if there is a Date
field in the text file formatted as YYMMDD.
CAUSE
Microsoft Access does not recognize the YYMMDD Date format in text files.
RESOLUTION
One workaround is to format the date as MMDDYY.
Another workaround is to import the YYMMDD field as a six-character Text
field. Next, create a new field in the table called, for example, NewDate,
with a data type of Date/Time. Then run an update query to derive the
NewDate field from the OldDate field as follows.
NOTE: In the following example, an underscore(_) is used as a
line-continuation character. Remove the underscore from the end
of the line when re-creating this example.
Query: ReformatDate
------------------------------------------------------------
Field Name: NewDate
Update to: Mid([OldDate],3,2) & "/" & Right([OldDate],2) & _
"/" & Left([OldDate],2)
If the Text field that contains the date has embedded slashes (/), such as
1/5/94, then the starting value in the Mid() function must be 4 rather
than 3. In the example above, you would change the Mid() function to look
as follows:
Mid([Olddate],4,2)
MORE INFORMATION
In Microsoft Access 7.0 and 97, when importing a fixed-width text file
with a Date field formatted as YYMMDD, Microsoft Access generates an
Import Error log table, but does not import the data for the Date field.
If you try to import the text file as delimited, the data is imported, but
Microsoft Access changes the format from Date to Number or Text, depending
upon the date delimiter. In Microsoft Access 2.0 or earlier, the file will
not be imported.
Modification Type: | Major | Last Reviewed: | 10/20/2003 |
---|
Keywords: | kb3rdparty kbprb KB93727 |
---|
|