Delimited Text Date Format (Access 2000 SP3)

[brady]

Every quarter we need to upload a delimited text file generated from a parameter query (based on the date field) using the | separator to an online reporting site. They take our records and merge them into a database at their end that requires a MM/DD/YYYY format for date fields. Using Access 2000's user interface menu items ( File > Export... ) will fire up a wizard to produce the needed delimited text file by choosing "Text Files (*.txt;*.csv;*.tab;*.asc)" from the "Save as type:" dropdown menu. However, the date field format changes in the resulting .txt file. The Date/Time field setting for the field in question is set both in Windows 2000 (SP-4) Regional Options and in Access 2000's (SP-3) table properties to 'Short Date' (i.e.; M/d/yyyy) and this is all that appears in the user interface when viewing records in the database. However, when creating a delimited file, it automatically adds the time (H:mm:ss) too. The resulting .txt field will show:

|8/28/2003 0:00:00|

The online database that we upload to will not accept records that do not match their date format. When I delete the " 0:00:00", everything uploads fine. Do you know of some way to successfully produce such a delimited .txt file using VBA that can control Access' default date output format? Or is there some easier fix for the resulting .txt file produced by the wizard?

Thanks for your consideration of this dilemma!
Brady

[HansV]

The usual advice is to create a query with a calculated field that formats the text:

Format([MyDateField],"mm/dd/yyyy")

By default, the dates will be enclosed in quotes since the calculated field is a text field; there is a setting for this in the Export Text Wizard.

[brady]

Excellent! Thank you Hans