ACC: Exporting Date Fields to Text Includes Time Format

• Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

SYMPTOMS

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.

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.

1. Perform steps 1-4 in the Steps to Reproduce section of this article.

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

3. Save the query and close it.

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

STATUS

This behavior is by design.

MORE INFORMATION

Steps to Reproduce Behavior

1. Open the sample database Northwind.mdb (or NWIND.MDB in versions 1.x and 2.0).

2. Create the following new table in Design view:

         Table: TestDate
         -----------------------
         Field Name: Birthday
            Data Type: Date/Time
   
   

3. Save the table and close it. You do not need to create a primary key.

4. Open the table in Datasheet view, add the following record, and then close the table:

         11/15/67
   

5. Perform the following steps to export and view the data.

   In Microsoft Access 7.0 and 97:

       a. Select the TestDate table in the Database window, and then click
          Save As/Export on the File menu.
   

       b. In the Save As dialog box, click To an External File or Database,
          and then click OK.
   

       c. In the Save Table 'TestDate' As dialog box, select Text Files in
          the Save as type box, and then click Export.
   

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

       e. Click Cancel to return to the Database window.
   

   In Microsoft Access 1.x and 2.0:

       a. On the File menu, click Export.
   

       b. In the Export dialog box, select Text (Delimited), and then
          click OK.
   

       c. In the Select Microsoft Access Object dialog box, select the
          TestDate table, and then click OK.
   

       d. In the Export to File dialog box, type a unique name in the File
          Name box, and then click OK.
   

       e. Click OK in the Export Text Options dialog box.
   

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

REFERENCES