Microsoft KB Archive/209716

= ACC2000: Cannot Import YYMMDD Dates in Fixed-Width Text Files =

Article ID: 209716

Article Last Modified on 6/29/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q209716





SYMPTOMS
Novice: Requires knowledge of the user interface on single-user computers.

Microsoft Access will not import fixed-width text files if there is a Date field in the text file that is 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.   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
When you import a fixed-width text file with a Date field that is formatted as YYMMDD, Microsoft Access generates an Import Errors 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.

Additional query words: convert year month day prb

Keywords: kbprb kb3rdparty KB209716

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.