ACC2: Importing a Text File with Dates in DDMMMYY Format

 Last reviewed: May 12, 1997
Article ID: Q131933
The information in this article applies to:
  • Microsoft Access versions 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" (without the quotation marks), 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: 

   ARTICLE-ID: Q159323
   TITLE     : 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 : IsmTxtd kb3rdparty
Version : 2.0
Platform : WINDOWS
Hardware : X86
Issue type : kbprb
Resolution Type : Info_Provided

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: May 12, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.