Hi Chris,
Whenever you format a field,
the result is always a string...
so it will have quotes around it
when you export it.
If you export a date field
(without formatting), I don't
know of a way to not have the
time portion show up in the
export file...but you won't have
quotes around it.
You could apply DateValue to
date field, so time portion will
always be 00:00:00 if that will help.
The only way to do what you want
(to my knowledge) would be to open
a recordset to your query, and write
your own text file in vb.
Something like:
Private Sub cmdExportQuery_Click()
On Error GoTo Err_cmdExportQuery
Dim hFile As Long
Dim varPath As Variant
Dim strTextLine As String
Dim lngRecCount As Long
Dim rst As DAO.Recordset
Dim varReturn As Variant
Dim strMsg As String
DoCmd.Hourglass True
varPath = "C:\newfile.txt"
Set rst = Currentdb.OpenRecordset("nameofyourquery", dbOpenDynaset)
'**** collect all fields from rst into strTextLine *****
With rst
.MoveFirst
lngRecCount = 0
strTextLine = ""
Do While (Not .EOF)
lngRecCount = lngRecCount + 1
' tell user what you are doing by writing to Status Bar
strMsg = " Now Outputting Record " & lngRecCount & " to the file :" &
varPath
varReturn = SysCmd(acSysCmdSetStatus, strMsg)
strTextLine = strTextLine & Chr(34) & !field1 & Chr(34) & ", "
strTextLine = strTextLine & Chr(34) & !field2 & Chr(34) & ", "
strTextLine = strTextLine & Chr(34) & !field3 & Chr(34) & ", "
strTextLine = strTextLine & Chr(34) & !field4 & Chr(34) & ", "
strTextLine = strrTextLine & Format(!invoicedate, "mm/dd/yyyy") &
vbCrLf
.MoveNext
Loop
End With
rst.Close
'**** Save the collected string to the text file *****
'Will be using Put method so want to first
'delete file if it already exists
On Error Resume Next
Kill varPath
On Error GoTo Err_cmdExportQuery
'Get a new file handle
hFile = FreeFile
'Open the varPath to write the memo
Open varPath For Binary Access Write As #hFile
'write collected lines to file
Put #hFile, 1, strTextLine
' done so close file.
Close #hFile
varReturn = SysCmd(acSysCmdSetStatus, "Export Process Complete.")
Exit_cmdExportQuery:
Set rst = Nothing
Reset
DoCmd.Hourglass False
Exit Sub
Err_cmdExportQuery:
MsgBox "Error: " & Err.Description & " (" & Err.Number & ")"
Resume Exit_cmdExportQuery
End Sub
You'll have to change things like names for
txt file, query, fieldx, etc.
You will need to reference DAO library.
And above is a "copy/paste/adapt" process
so may have missed something.
Please respond back if I have misunderstood
or something is not clear.
Good luck,
Gary Walter
Chris said:
I am trying to query a small table to then export the
queried data to a text file which is comma delimited. My
issue is the field which has a date format of mm/dd/yyyy
has double quotes around it but I can not have this field
quoted. I am using this statement in the query to only
extract the date (no time) InvoiceDate: Format([invoice
date],"mm/dd/yyyy")
If I remove the double quotes in the above statement I
receive an error about expecting parameters. The rest of
the exported data must be double quoted. Here is a sample
of the desired txt file format. "US","332439","02-
198250","73.44","0.00","EA",10/23/2003
What can I do to get this export to work right?
