ACC2: Importing a Text File with Dates in DDMMMYY Format

• Microsoft Access 2.0

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