
	Search KbAlertz Advanced Search

Receive Microsoft Knowledge Base articles by E-Mail?

Every night we scan the Microsoft Knowledge Base. If technologies you're interested in are updated, we'll send you an e-mail. You only get one e-mail a day, and only when new articles are added.

Click here to create a
FREE account

Already have an account?
[Click here to Login]

Microsoft Knowledge Base Article

This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved. Terms of Use | Trademarks

Ads by Lake Quincy Media

Article ID: 208318 - Last Review: July 15, 2004 - Revision: 1.1

ACC2000: Importing Text File with Dates in DDMMMYY Format (Access 2000)

View products that this article applies to.

This article was previously published under Q208318

Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access database (.mdb).

SYMPTOMS

When you import a delimited text file that has a field that contains values in a Medium Date format (DDMMMYY), the data for that field is not imported and an import errors table is created.

Back to the top

CAUSE

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

Back to the top

RESOLUTION

To import Medium Dates in a delimited text file into a new table, follow these steps:

On the File menu, point to Get External Data, and then click Import.
In the Import dialog box, click Text Files in the Files of type box.
Find the text file that you want to import, select it, and then click Import to start the Import Text Wizard.
On the first screen of the wizard, click Delimited, and then click Next.
On the What delimiter separates your fields? screen, select a Delimiter and Text Qualifier depending on the format of the text file that you are importing. If the first line in the text file contains field names, check the First Row Contains Field Names check box. Click Next.
On the Where would you like to store your data? screen, click In a New Table, and then click Next.
On the You can specify information about each of the fields you are importing screen, click the column heading of the column that contains the dates to select that column. In the Data Type list, select Text. Click Next.
On the Microsoft Access recommends that you define a primary key screen, click the appropriate option: Let Access add Primary Key, Choose my own Primary Key, or No Primary Key." Click Finish. The wizard creates your table with a text field containing the dates in Medium Date format.
Open the new table in Design view and change the following properties of the Medium Date field:
```
   Data Type: Date/Time
   Format: Medium Date
					
```
When you save the table and close it, the text values are converted to dates.

You can use the preceding steps to import a delimited text file and append it to an existing table. In step 6, click the option In an Existing Table instead of In a New Table, and make sure the existing table's date field is a Text data type. After you import the data, change the field's data type to Date/Time and set its Format property to Medium Date.

Back to the top

MORE INFORMATION

Steps to Reproduce Behavior

Open any text editor, such as Notepad.

Type the following four lines of delimited text:

   DDMMMYY,MMDDYY
   01-Dec-98,12/1/98
   31-Jan-99,1/31/99
   15-Mar-98,3/15/98

Save the file as TestDate.txt.
Start Microsoft Access and open any database.
On the File menu, point to Get External Data, and then click Import.
In the Import dialog box, click Text Files in the Files of type box.
Locate TestDate.txt, and then click Import to start the Import Text Wizard.
On the first screen of the wizard, click Delimited, and then click Next.
On the second screen of the wizard, click to select the First Row Contains Field Names check box, and then click Finish. Note that you receive a message that some of the data (the dates in Medium Date format) were not imported successfully.

Back to the top

REFERENCES

For more information about changing a field's data type, click Microsoft Access Help on the Help menu, type change a field's data type in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

For more information about formatting dates, click Microsoft Access Help on the Help menu, type format property - date/time data type in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

Back to the top

APPLIES TO

Microsoft Access 2000 Standard Edition

Back to the top

kbinterop kbprb KB208318

Back to the top

Community Feedback System

Very often, it takes hours to solve a problem. Very often, you've looked high and low, and have tried a lot of solutions. When you finally found it, chances are, it was because someone else helped you. Here's your chance to give back. Use our community feedback tool to let others know what worked for you and what didn't.

Please also understand that the community feedback system is not warranted to be correct, it's simply a system that we've built to let people try and help each other. If something in a feedback response doesn't make sense to you, or you're not comfortable making changes that the feedback talks about (like registry edits), please consult a professional.

Thank you for using kbAlertz.com Feedback System.

-- Scott Cate

Ads by Lake Quincy Media