Exporting sorted query as text-file (1 Viewer)

[jeffnation]

Hey all,

I'm having issues exporting a sorted query as a delimited text file.
I have 3 columns:

• column 1 not sorted
• column 2 not sorted
• column 3 sorted DESC

When I export the query, it defaults to ASC for all 3 columns.
I've tried to apply the ORDER BY clause in SQL view but it has no effect.
"ORDER BY Column3 DESC;"

It only works when I tick 'export data with formatting and layout' but I need text separated with single quotes.

Any ideas?

Thanks,
Jeff

 

[Gasman]

If you do not use that option, it will open as you wish.
You can also save the export steps?

 

[arnelgp]

you need to test this Sub:

'*********************
'* arnelgp
'*
'* parameters:
'*
'* strTable             table/query name or select statement
'* strOutput            path and filename to which it will saved (like: z:\test.txt)
'*                          if not supplied, file will be saved on same folder as the
'*                          db (text.txt)
'* strDelimiter         delimiter to use for export
'* bolExtraSpaceAbove   if you need extra space above your header, just passed True
'*

Public Sub DataToText(ByVal strTable As String, Optional ByVal strOutput As String = "", _
                            Optional ByVal strDelimiter As String = vbTab, _
                            Optional ByVal bolExtaSpaceAbove As Boolean = False)
    
    Dim db As DAO.Database
    Dim rs As DAO.recordSet
    Dim outfile As Integer
    Dim i As Integer
    Dim LineOfText As String
    
    If strOutput = "" Then strOutput = CurrentProject.path & "\Text.txt"
    If Dir(strOutput) <> "" Then Kill (strOutput)
    outfile = FreeFile
    Open strOutput For Output As #outfile
    Set db = CurrentDb
    Set rs = CurrentDb.OpenRecordset(strTable, dbOpenSnapshot)
    
    
    With rs
        If Not (.BOF And .EOF) Then
            .MoveFirst
            If bolExtaSpaceAbove Then
                Print #outfile, ""
            End If
            'if you want column headers, uncomment the following 5 lines of code
            For i = 0 To .fields.count - 1
                LineOfText = LineOfText & .fields(i).NAME & strDelimiter
            Next i
            LineOfText = Trim$(LineOfText)
            LineOfText = left$(LineOfText, Len(LineOfText) - Len(strDelimiter))
            Print #outfile, LineOfText

            'loop through records
            Do While Not .EOF
                LineOfText = ""
                'build up line of text
                For i = 0 To .fields.count - 1
                    LineOfText = LineOfText & Replace$(Nz(.fields(i)), "'", "''") & strDelimiter
                Next i
                LineOfText = Trim$(LineOfText)
                LineOfText = left$(LineOfText, Len(LineOfText) - Len(strDelimiter))
                'write line of text to file
                Print #outfile, LineOfText
                .MoveNext
            Loop
        End If
    End With
    
    Close #outfile
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    
End Sub

example:
DataToText "QueryName", "D:\output.txt", "'"