Microsoft KB Archive/103174

= ACC: Exporting Date Fields to Text Includes Time Format =

Article ID: 103174

Article Last Modified on 1/18/2007

-

APPLIES TO


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



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



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



RESOLUTION
There are two methods you can use to work around this behavior.

Method 1
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.

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

<div class="status_section">

STATUS
This behavior is by design.

<div class="moreinformation_section">

Steps to Reproduce Behavior
 Open the sample database Northwind.mdb (or NWIND.MDB in versions 1.x and 2.0).</li>  Create the following new table in Design view:

<pre class="fixed_text">     Table: TestDate ---     Field Name: Birthday Data Type: Date/Time

</li> Save the table and close it. You do not need to create a primary key.</li>  Open the table in Datasheet view, add the following record, and then close the table:

<pre class="fixed_text">     11/15/67

</li> 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.</li> In the Save As dialog box, click To an External File or Database, and then click OK.</li> In the Save Table 'TestDate' As dialog box, select Text Files in the Save as type box, and then click Export.</li> 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.</li> Click Cancel to return to the Database window.</li></ol>

In Microsoft Access 1.x and 2.0:

 On the File menu, click Export.</li> In the Export dialog box, select Text (Delimited), and then click OK.</li> In the Select Microsoft Access Object dialog box, select the TestDate table, and then click OK.</li> <li>In the Export to File dialog box, type a unique name in the File Name box, and then click OK.</li> <li>Click OK in the Export Text Options dialog box.</li> <li>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.</li></ol> </li></ol>

<div class="references_section">