When you export date fields to text, the time format is included Abstract (291245)
The information in this article applies to:
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:
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 then close it.
-
Export the query.
Method 2
You can have Access create a fixed-width text file by using the "Text (Fixed Width)" 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.
REFERENCESFor 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: | Minor | Last Reviewed: | 8/11/2004 |
---|
Keywords: | kbformat kbprb KB291245 |
---|
|