I am exporting some long dates with the field format being defined within the query. In the query results it shows Tuesday, February 24, 2004. When the data is exported to a tab delimited file the date shows as 2/24/2004 0:00:00. How do I get it in the correct format???

I was never happy with transfer text and the assumptions it made in regard to formatting data. If you don't mind writing a bit of code you can use a bit of old fashioned Basic (and VB/A) code to control the process. You could use the file system object as well, but that requires a reference to the Scripting libraries which many companies refuse to enable. The conversion of text file data to access tables is even worse as no data validation can take place in the import. If you have embedded tabs in the data you are ouputting to a tab delimited file with transferdatabase, you can't strip them out and the exported data ends up in incorrect columns often resulting in type conversion errors and the whole shooting match fails. Using file i/o, you have complete control of all aspects of the format of each line and have the ability to log missed records and continue despite errors. Have a look at:

http://puma.agron.ksu.edu/~sgsax/

click the link to 'My Downloads'

and then open the link:

'File I/O in VB'

There are 3 examples. The 2nd is outputting from recordset to a semi colon delimited file. Change the line:

strOutput = rst!Field1 & ";" & rst!Field2 & ";" & rst!Field3

to

strOutput = rst!Field1 & vbTab & Format$(rst!Field2, "dddd, mmmm dd, yyyy") & vbTab & rst!Field3

in the case where the 2nd field of the recordset is the formatted date.


Ciao
Jürgen Welz
Edmonton AB Canada
[email protected]

Thanks, I'll try it tomorrow.