Trouble with DoCmd.TransferText

[Dor100]

When I transfer text from a query to a .txt file through VBA or a Macro, date fields include the unwanted 00:00:00 time element, i.e., “01/18/2006 00:00:00.” I need to eliminate this so it doesn’t show up in merge docs that use the output for data.

Why is it that when I export the same query to a .txt file through menu commands (File - Export), the dates appear exactly as desired without the 00:00:00? Is there a way to tweak the VBA to automate that? The line below is what I’m working with:

DoCmd.TransferText acExportMerge, "", "QueryName", "C:\Folder\FileName.txt", True, ""

 

[Dor100]

Ok, the answer is:

Create an export spec in which you blank out the Time Delimiter field. Then add it to the code:

DoCmd.TransferText acExportMerge, "SpecName", "QueryName", "C:\Folder\FileName.txt", True, ""

Interestingly, you can't get the text export wizard to appear in order to make the spec in Acc XP (at least in my copy) by going through the motions of exporting as Microsoft Word Merge (*.txt), but you have to export as Text Files (*.txt, etc.) instead.

 

[JeffHawkins]

Having the same problem in Acc2K, I blanked out the Time delimeter and it just shows up:

2/22/06 0000

 

[Ed2020]

Give this a try:

Public Sub CreateExportFile(strFileName As String, strTableName As String)
On Error GoTo ErrorHandler
'Exports a text file from the specified table to the specified filepath
'Originally written to prevent Access appending .00 to a single or double
'numeric field
'Can also be used to change format that dates are exported in
'Inputs: strFileName - the full filepath for the export file
' strTableName - the name of the table to export from
'Will work for any table containing any number/type of fields.
'Written by Ed Metcalfe, 06/09/2001.
'Updated by Ed Metcalfe, 8/11/2001 - now removes carriage returns and line feeds from fields

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim lngRecordCount As Long
Dim lngRecordNumber As Long
Dim strConcatenatedRecord As String
Dim intCounter As Integer
Dim strSplit As String

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strTableName, dbOpenDynaset)
If rst.RecordCount = 0 Then Exit Sub
rst.MoveLast
lngRecordCount = rst.RecordCount
lngRecordNumber = 1

rst.MoveFirst

'Delete the previous file if it exists
If Dir(strFileName) <> "" Then
Kill strFileName
End If

Do
'Reset the counter to zero and string variable to empty
intCounter = 0
strConcatenatedRecord = Empty

'Cycle through record and get each field value store in strConcatenatedRecord
'If the field type is text (10) then append a text qualifier
Do
If rst.Fields(intCounter).Type = dbText Then
strConcatenatedRecord = strConcatenatedRecord & "," & """" & rst.Fields(intCounter).Value & """"
intCounter = intCounter + 1
'ElseIf rst.Fields(intCounter).Type = dbDate Then
'strConcatenatedRecord = strConcatenatedRecord & "," & Format(rst.Fields(intCounter).Value, "dd/mm/yyyy")
'intCounter = intCounter + 1
Else: strConcatenatedRecord = strConcatenatedRecord & "," & rst.Fields(intCounter).Value
intCounter = intCounter + 1
End If
Loop Until intCounter = rst.Fields.Count

'Remove the leading comma from the string
strConcatenatedRecord = Right(strConcatenatedRecord, Len(strConcatenatedRecord) - 1)

'Copy the record to the text file
If lngRecordNumber = 1 Then
Open strFileName For Output As #1
Print #1, strConcatenatedRecord
Else
Print #1, strConcatenatedRecord
End If

'Move to next record, increase record counter by 1 and loop
rst.MoveNext
lngRecordNumber = lngRecordNumber + 1

Loop Until rst.EOF

'Close the file
Close #1

'Close database and recordset connections and set variables to nothing to release system
'resources
rst.Close
dbs.Close
Set rst = Nothing
Set dbs = Nothing

ExitHere:
Exit Sub

ErrorHandler:
MsgBox Err.Description
Close #1
Resume ExitHere

End Sub

Please do not feed the trolls.....