Export Query with Short Date (XP)

Topic

New Reply

I am exporting a recordset to a text file. One of the fields in the recordset is a date/time field formatted to Short Date. When I export the data, I get mm/dd/yyyy 0:00:00. I need to export the mm/dd/yyyy without the hours minutes and seconds.

I am producing this:
“Joe”,”Blow”,”WC”,10/25/2003 0:00:00,1
We want this:
“Joe”,”Blow”,”WC”,10/25/2003,1
At first glance, this looked fairly straight forward. I changed the date field in the query to
SomeDate: format([ReportDate],”mm/dd/yyyy”)
and ended up with “mm/dd/yyyy”. However, the recipient of this file can not have quotes around the date.

So, how can I get rid of the hours:minutes:seconds without putting quotes around the date?

Thanks,

Reply | Quote

Replies

Without jumping through lots of hoops, I don’t think there is any way to get rid of the quotes – dates are considered a text field by the delimited text export routines. You could try using a null delimiter character, but that’s problematic if you have any sort of special characters in your text, such as apostrophe, quote or comma. If it isn’t a ton of data, you could simply use a Find/Replace in Word. If you do it repeatedly, you could create a macro in Word. Another option would be to bring it into Excel. I believe Excel will create CSV file dates without the quotes, but try it to be sure.

Reply | Quote

I don’t know any way to change the way Access exports text files. As alternative you can open a recordset and write the data to a text file the old-fashioned way, using VB file input/output statements. Example:

Sub WriteToTextFile()
On Error GoTo Err_Handler

Dim rst As ADODB.Recordset
Dim strSQL As String
Dim lngCount As Long
Dim n As Long
Dim strFile1 As String
Dim strFile2 As String
Dim strMsg As String

strSQL = “SELECT Field1, Field2, Field3 ” & _
“FROM Table1 ORDER BY Field1;”

Set rst = New ADODB.Recordset
rst.Open strSQL, CurrentProject.Connection, adOpenStatic, adLockOptimistic

lngCount = rst.RecordCount
If lngCount > 0 Then
rst.MoveFirst

strFile1 = “C:AccessTest1.txt”
strFile2 = “C:AccessTest2.txt”
Open strFile1 For Output As #1
Open strFile2 For Output As #2
‘ If field names required:
‘ Write # statement will automatically add delimiters:
Write #1, rst.Fields(0).Name, rst.Fields(1).Name, rst.Fields(2).Name
Write #2, rst.Fields(0).Name, rst.Fields(1).Name, rst.Fields(2).Name

For n = 1 To lngCount
‘ Field1 = Text, 2 = Number, 3 = Date
‘ If using Print # instead of Write #, must provide your own delimiters:
Print #1, Chr$(34) & rst!Field1 & Chr$(34) & “,” & rst!Field2 & “,” & rst!Field3
Write #2, rst!Field1, CInt(rst!Field2), rst!Field3
rst.MoveNext
Next n
Close #1
Close #2
Else
MsgBox “No records.”, vbExclamation, “NO RECORDS”
End If
rst.Close

Exit_Sub:
Set rst = Nothing
Exit Sub
Err_Handler:
strMsg = “Error No ” & Err.Number & “: ” & Err.Description
Beep
MsgBox strMsg, vbExclamation, “ERROR MESSAGE”
Resume Exit_Sub

End Sub

The example creates 2 text files using VB Print and Write (sequential file access) statements. Write # will automatically add delimiters when file is written; with Print #, you must add the delimiters and field separators. Note that although Field2 is defined as Number (Integer) I had to explicitly use CInt function in the Write statement, or it was written to file with quote delimiters (“123”, etc). Regarding dates, according to Help, for Write “Date data is written to the file using the universal date format” (#2003-10-31# format), while with Print “Date data is written to the file using the standard short date format recognized by your system.” See attached illustration for comparison of sample output data written to file by the procedure. For more info, see VBA Help for Open, Print #, Write #, etc statements.

HTH

Reply | Quote

I don’t know any way to change the way Access exports text files. As alternative you can open a recordset and write the data to a text file the old-fashioned way, using VB file input/output statements. Example:

Sub WriteToTextFile()
On Error GoTo Err_Handler

Dim rst As ADODB.Recordset
Dim strSQL As String
Dim lngCount As Long
Dim n As Long
Dim strFile1 As String
Dim strFile2 As String
Dim strMsg As String

strSQL = “SELECT Field1, Field2, Field3 ” & _
“FROM Table1 ORDER BY Field1;”

Set rst = New ADODB.Recordset
rst.Open strSQL, CurrentProject.Connection, adOpenStatic, adLockOptimistic

lngCount = rst.RecordCount
If lngCount > 0 Then
rst.MoveFirst

strFile1 = “C:AccessTest1.txt”
strFile2 = “C:AccessTest2.txt”
Open strFile1 For Output As #1
Open strFile2 For Output As #2
‘ If field names required:
‘ Write # statement will automatically add delimiters:
Write #1, rst.Fields(0).Name, rst.Fields(1).Name, rst.Fields(2).Name
Write #2, rst.Fields(0).Name, rst.Fields(1).Name, rst.Fields(2).Name

For n = 1 To lngCount
‘ Field1 = Text, 2 = Number, 3 = Date
‘ If using Print # instead of Write #, must provide your own delimiters:
Print #1, Chr$(34) & rst!Field1 & Chr$(34) & “,” & rst!Field2 & “,” & rst!Field3
Write #2, rst!Field1, CInt(rst!Field2), rst!Field3
rst.MoveNext
Next n
Close #1
Close #2
Else
MsgBox “No records.”, vbExclamation, “NO RECORDS”
End If
rst.Close

Exit_Sub:
Set rst = Nothing
Exit Sub
Err_Handler:
strMsg = “Error No ” & Err.Number & “: ” & Err.Description
Beep
MsgBox strMsg, vbExclamation, “ERROR MESSAGE”
Resume Exit_Sub

End Sub

The example creates 2 text files using VB Print and Write (sequential file access) statements. Write # will automatically add delimiters when file is written; with Print #, you must add the delimiters and field separators. Note that although Field2 is defined as Number (Integer) I had to explicitly use CInt function in the Write statement, or it was written to file with quote delimiters (“123”, etc). Regarding dates, according to Help, for Write “Date data is written to the file using the universal date format” (#2003-10-31# format), while with Print “Date data is written to the file using the standard short date format recognized by your system.” See attached illustration for comparison of sample output data written to file by the procedure. For more info, see VBA Help for Open, Print #, Write #, etc statements.

HTH

Reply | Quote