Date Format Changes during export

[Guest]

I have 3 x Date/Time Fields in a table which I have formatted dd/mm/yyyy to
meet the requirements of a transmission specification. I have also set the
dd/mm/yyyy format to corresponding Form Control Fields and the query which
generates the export table.
To this point all is working fine...
The next step requires me to export the data to a comma deliminated .txt
file and transmit by email to a data repository. This worked OK!
Until... the data repository made addmendments to their transmission
specifications and now require the text identifiers " " to be removed. Each
time I remove them and save the specification the date format changes to
dd/mm/yyyy 0:00:00 and of course the records are rejected due to incorrect
date format.
Temporarily I have worked around this by generating the export file and then
performing a replace action (Replace 0:00:00 with blank). Fine for me but my
end users are supposed to be able to generate and send this file not me?
Any I deas would be appreciated!

 

[Ken Snell \(MVP\)]

Use an export specification that says to use no text qualifiers, then
specify that export specification in the DoCmd.TransferText action as the
second argument (see Help).

To set up an export specification, do the export process manually and go all
the way to the final screen in the wizard but don't click the "Finish"
button. Instead, click the Advanced button and save the specifications with
a name. Then cancel the export.

You then use that name as the name of the export specification.

 

[Guest]

Thanks Ken...Yes I have specified no text qualifiers in the export
specification and have no issue with the removal of text qualifiers...My
problem is the date format which only changes when I remove text qualifiers.

 

[Ken Snell \(MVP\)]

Just tested exporting a query based on a table of dates, and I told the
export "no text qualifier":

SELECT Format([D],"mm\/dd\/yyyy") AS TD
FROM TableName;

This exported this result:

07/18/2005
07/19/2005
07/20/2005
07/21/2005
07/22/2005
07/23/2005
07/24/2005
07/25/2005
07/26/2005
07/27/2005
07/28/2005


If I export the table directly, then I get the result you describe:

7/18/2005 0:00:00
7/19/2005 0:00:00
7/20/2005 0:00:00
7/21/2005 0:00:00
7/22/2005 0:00:00
7/23/2005 0:00:00
7/24/2005 0:00:00
7/25/2005 0:00:00
7/26/2005 0:00:00
7/27/2005 0:00:00
7/28/2005 0:00:00

So you must use a query as the basis of the export.