ACC2000: Exporting Date Fields to Text Includes Time Format (209268)


The information in this article applies to:

  • Microsoft Access 2000
This article was previously published under Q209268
Moderate: Requires basic macro, coding, and interoperability skills.

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

For a Microsoft Access 2002 version of this article, see 291245.

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 with a calculated field to format the date field appropriately, and then export the results of the query. To do so, follow these steps
  1. Perform steps 1 through 4 in the "Steps to Reproduce Behavior" section later in 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

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 10 characters in the Export specification. This truncates the time portion of the date field during export.

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

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

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

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

REFERENCES

For more information about exporting data to other file formats, click Microsoft Access Help on the Help menu, type exporting data, data formats in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.
Modification Type:MajorLast Reviewed:6/24/2004
Keywords:kbprb kbusage KB209268
©2004 Microsoft Corporation. All rights reserved.