Microsoft KB Archive/131933

= ACC2: Importing a Text File with Dates in DDMMMYY Format =

Article ID: 131933

Article Last Modified on 6/25/2002

-

APPLIES TO


 * Microsoft Access 2.0 Standard Edition

-



This article was previously published under Q131933



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



SYMPTOMS
When you import a delimited text file containing a field with values in a Medium Date format (DDMMMYY) with the Create New Table option, the imported date field has a Text data type rather than a Date/Time data type. When you import a delimited text file containing a field with values in a Medium Date format (DDMMMYY) with the Append to Existing Table option, you receive a "type conversion" error.



CAUSE
Microsoft Access does not recognize the Medium Date format when importing dates in a delimited text file.



RESOLUTION
When you import a delimited text file with the Create New Table option, change the field's data type to Date/Time and set the field's Format property to Medium Date.

When you import a delimited text file with the Append to Existing Table option, make sure the table's date field has a Text data type. Once you import the data, change the field's data type to Date/Time and set its Format property to Medium Date.



Steps to Reproduce Behavior
 Open any text editor, such as NOTEPAD.  Type the following four lines of delimited text:

      DDMMMYY,MMDDYY 01-Dec-95,12/1/95 31-Jan-96,1/31/96 15-Mar-94,3/15/94  Save the file as Testdate.txt. Start Microsoft Access and open any database. On the File menu, click Import.</li> In the Data Source box, select Text(Delimited), and then click OK.</li> In the File Name box, type testdate.txt, and then click Import.</li> In the Import Text Options dialog box, check the First Row Contains Field Names check box, and then click OK.</li> When you receive the "Import To TESTDATE Finished" message, click OK.</li> In the Select File dialog box, click Close.</li> Open the TESTDATE table in Design view. Note that the DDMMMYY field has a Text data type while the MMDDYY field has a Date/Time data type.</li></ol>

<div class="references_section">