Problems formatting text file generated from using OutputTo Method

[nancylynn]

Using Access 2000-
I am using the OutputTo method on a form, but when it exports the data, the data is formatted with all these borders around each piece of data. All I need is a comma delimited text file to be exported, without including the field names. Only the data in the recordset. Here is the code I have currently:

Private Sub Form_Dirty(Cancel As Integer)
DoCmd.OutputTo acOutputForm, "frmTest", acFormatTXT, "test.txt"
End Sub

 

[jfischer]

Instead of using DoCmd.OutputTo, try using DoCmd.TransferText, as follows:

DoCmd.TransferText acExportDelim, , Me.RecordSource, "C:\Temp\Test.txt", False

This will produce a comma delimited version of your form's record source with no field lables in the first record.

 

[nancylynn]

Thanks! That works great. Now I need to get rid of the space between quotes (from null values). I need the exported data to look like this:
"33338","doe","nancy","","2"
instead of this:
"3338","doe","nancy"," ","2"

 

[jfischer]

You'll get a blank between the quotes for a field that actually has a blank stored in the database. The TransferText command cannot change the stored data on the fly.

If you don't want to change the database contents for the export, you could write a query based on your form's record source that checks for blanks in a field and substitutes a null string. For example:

Expr1: IIf([Field4]>" ",[Field4],"")

Then, use the query as your export record source in the TransferText command. For example:

DoCmd.TransferText acExportDelim, , "qryMyFormNoBlanks", "C:\Temp\Test.txt", False

 

[nancylynn]

Thanks so much! Works great!