Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Jun 2001
    Virginia, USA
    Thanked 0 Times in 0 Posts

    Export Query with Short Date (XP)

    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:
    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? <img src=/S/confused.gif border=0 alt=confused width=15 height=20>


  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Evergreen, CO, USA
    Thanked 58 Times in 58 Posts

    Re: Export Query with Short Date (XP)

    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.

  3. #3
    Bronze Lounger
    Join Date
    Nov 2001
    Arlington, Virginia, USA
    Thanked 3 Times in 3 Posts

    Re: Export Query with Short Date (XP)

    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

    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
    Next n
    Close #1
    Close #2
    MsgBox "No records.", vbExclamation, "NO RECORDS"
    End If

    Set rst = Nothing
    Exit Sub
    strMsg = "Error No " & Err.Number & ": " & Err.Description
    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.

    Attached Images Attached Images

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts