Microsoft KB Archive/291245

= When you export date fields to text, the time format is included Abstract =

Article ID: 291245

Article Last Modified on 8/11/2004

-

APPLIES TO


 * Microsoft Access 2002 Standard Edition

-



This article was previously published under Q291245



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

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

For a Microsoft Access 2000 version of this article, see 209268.



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
Access stores date and time values for all date fields. When you export the data to a text file, 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 to work around this behavior.

Method 1
Create a query that has a calculated field to format the Date/Time field appropriately, and then export the results of the query. To do so, follow these steps  Open the sample database Northwind.mdb.  Create the following new table in Design view:   Table: TestDate Field Name: Birthday Data Type: Date/Time  Save the table, and then close it. You do not have to create a primary key.  Open the table in Datasheet view, add the following record, and then close the table:   04/19/93   Create the following new query based on the TestDate table: <pre class="fixed_text">  Query: ExportDates Type: Select Query

Field: Birthday2: Format([Birthday], &quot;mm/dd/yy&quot;) NOTE: The calculated field name cannot match the name of any other field that is included in the query. </li> Save the query, and then close it.</li> Export the query.</li></ol>

Method 2
You can have Access create a fixed-width text file by using the &quot;Text (Fixed Width)&quot; export format. By using this method, you can set the size of the Date/Time field to 10 characters in the Export Specification dialog box. This will truncate the time portion of the Date/Time field when the file is exported.

NOTE: This method assumes that you have the Four Digit Years and Leading Zeroes in Dates options selected in the Export Specification dialog box.

NOTE: Most common text exports require the data is delimited by using the Text (Delimited) format. Method 2 is not useful in these cases.

<div class="moreinformation_section">

MORE INFORMATION
CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

Steps to Reproduce Behavior
<ol> Open the sample database Northwind.mdb.</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">  04/19/93 </li> Select the TestDate table in the Database window, and then click Export on the File menu.</li> In the Export Table dialog box, select Text Files in the Save as type box, and then click Save.</li> In the Export Text Wizard, note that the data appears in the Sample export format box as 04/19/93 0:00:00.</li> Click Cancel to return to the Database window.</li></ol>

<div class="references_section">