ACC: Exporting Date Fields to Text Includes Time Format (103174)
The information in this article applies to:
- Microsoft Access 1.0
- Microsoft Access 1.1
- Microsoft Access 2.0
- Microsoft Access for Windows 95 7.0
- Microsoft Access 97
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.
STATUS
This behavior is by design.
REFERENCES
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.
Modification Type: | Major | Last Reviewed: | 5/6/2003 |
---|
Keywords: | kbprb kbusage KB103174 |
---|
|