ACC2: Importing a Text File with Dates in DDMMMYY Format

ID: Q131933

The information in this article applies to:

SYMPTOMS

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

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.


MORE INFORMATION

Steps to Reproduce Behavior


  1. Open any text editor, such as NOTEPAD.


  2. 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


  3. Save the file as Testdate.txt.


  4. Start Microsoft Access and open any database.


  5. On the File menu, click Import.


  6. In the Data Source box, select Text(Delimited), and then click OK.


  7. In the File Name box, type testdate.txt, and then click Import.


  8. In the Import Text Options dialog box, check the First Row Contains Field Names check box, and then click OK.


  9. When you receive the "Import To TESTDATE Finished" message, click OK.


  10. In the Select File dialog box, click Close.


  11. 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.



REFERENCES

For more information about this issue in Microsoft Access 7.0 or above, please see the following article in the Microsoft Knowledge Base:

Q159323 ACC: Importing Text File with Dates in DDMMMYY Format (95/97)

For more information about changing a field's data type, search for "data type conversion," and then "Changing a Field Data Type" using the Microsoft Access Help menu.

For more information about formatting dates, search for "date formats," and then "Format Property - Date/Time Data Types" using the Microsoft Access Help menu. 


Keywords          : kb3rdparty IsmTxtd 
Version           : 2.0
Platform          : WINDOWS 
Issue type        : kbprb

Last Reviewed: April 26, 1999