Export DB Query to a Text File 1

[Kiwiman]

I am trying to export a database query to a csv file. This has been successful for the most part, but I have come across a couple of problems.

1. The first problem is that the resulting file does not have any headers, only the data fields. The output only contains 6 fields and should be named Month, Reference,Account,Dr,Cr,Description.



2. The second problem, is that the resulting date in the text file shows as "31/01/2006 00.00". Is there any way of getting rid of the time portion of the date field?

Relevant code is:

Private Sub cmdReceivables_Click()
'Stop
Dim strDocName As String
Dim strExport As String
Dim strFileName As String
Dim strCoy As String, strDate As String, strFormat As String


strDocName = "JOURNALEXPORT" ' Name of the query to export
strExport = "JOURNAL" 'Export Specification
strCoy = Me.cboCoy ' name of Combo box specifying company
strFormat = "mmm-yy"
strDate = Format(Me.cboDate, strFormat)

strFileName = "C:\Documents and Settings\LLockett\My Documents\Work\General Ledger\" ' 
strFileName = strFileName & strCoy
strFileName = strFileName & strDate
strFileName = strFileName & "JNLASSETS.csv" ' File location and name


DoCmd.TransferText acExportDelim, strExport, strDocName, strFileName, False

    
End Sub

 

[PHV]

the resulting file does not have any headers
DoCmd.TransferText acExportDelim, strExport, strDocName, strFileName, [!]True[/!]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SeeThru]

For the date, you could use a custom date format. Put this into a query and export the query instead of the table.

 

[Kiwiman]

Thanks for the help. The first problem has been solved ( a combination of PHV's post and slightly changing the Export Spec to unchecking the "First Row Contains Field Names" check box).

The second problem is still occurring. I have the following date format on the field in the query:

" format(datevalue(tblDateConv.OracleDate),"dd/mm/yyyy") AS [Month]"

But this still returns "31/01/2006 00:00" in the text file. I have tried it with without the datevalue part, but get the same result. I have tried with other formats, but again no joy.