Microsoft KB Archive/103174
Article ID: 103174
Article Last Modified on 1/18/2007
- Microsoft Access 1.0 Standard Edition
- Microsoft Access 1.1 Standard Edition
- Microsoft Access 2.0 Standard Edition
- Microsoft Access 95 Standard Edition
- Microsoft Access 97 Standard Edition
This article was previously published under Q103174
Moderate: Requires basic macro, coding, and interoperability skills.
When you export a table that includes a Date/Time field to a text file, Microsoft Access automatically adds the time format to the date in the exported text file. The Date/Time field appears in the text file in mm/dd/yy hh:nn:ss format.
Microsoft Access stores date and time values for all date fields. When you export the data to a text file, Microsoft Access exports both the date and the time. If there is no data for the time portion of a date, the time appears as 0:00:00, indicating 12 A.M.
There are two methods you can use to work around this behavior.
Create a query with a calculated field to format the date field appropriately, and then export the results of the query.
NOTE: This method does not work in Microsoft Access 1.x since you cannot export a query in those versions. Instead, use a Make Table query in step 2, and then export the new table.
- Perform steps 1-4 in the "Steps to Reproduce Behavior" section of this article.
Create the following new query based on the TestDate table:
Query: ExportDates ------------------------------------------------ Type: Select Query Field: Birthday2: Format([Birthday], "mm/dd/yy")
NOTE: The calculated field name cannot match the name of any other field that is included in the query.
- Save the query and close it.
- Export the query.
Microsoft Access can create a fixed width text file, using the "Text (Fixed Width)" export format. Using this method, you can set the size of the date field to 8 characters in the Export specification. This truncates the time portion of the date field during export.
NOTE: Most common text exports require the data to be delimited using the Text (Delimited) format. Method 2 is not useful in these cases.
This behavior is by design.
Steps to Reproduce Behavior
- Open the sample database Northwind.mdb (or NWIND.MDB in versions 1.x and 2.0).
Create the following new table in Design view:
Table: TestDate ----------------------- Field Name: Birthday Data Type: Date/Time
- Save the table and close it. You do not need to create a primary key.
Open the table in Datasheet view, add the following record, and then close the table:
- Perform the following steps to export and view the data.
In Microsoft Access 7.0 and 97:
- Select the TestDate table in the Database window, and then click Save As/Export on the File menu.
- In the Save As dialog box, click To an External File or Database, and then click OK.
- In the Save Table 'TestDate' As dialog box, select Text Files in the Save as type box, and then click Export.
- In the Export Text Wizard (or Text Export Wizard in version 7.0), note that the data appears in the Sample export format box as 11/15/67 0:00:00.
- Click Cancel to return to the Database window.
In Microsoft Access 1.x and 2.0:
- On the File menu, click Export.
- In the Export dialog box, select Text (Delimited), and then click OK.
- In the Select Microsoft Access Object dialog box, select the TestDate table, and then click OK.
- In the Export to File dialog box, type a unique name in the File Name box, and then click OK.
- Click OK in the Export Text Options dialog box.
- Open the exported text file in any text editor (Notepad, for example) and note that the date appears as 11/15/67 0:00:00.
For more information about exporting data to other file formats, search the Help Index for "exporting data, data formats," or ask the Microsoft Access 97 Office Assistant.
Keywords: kbprb kbusage KB103174