How to pass SQL Query to DoCmd.TransferText

[Jarekb]

I have a button that will transfer the results of a query to a formatted text file. Here's the code,it works fine.

Public Sub BatchButton_Click()

Select Case MsgBox("Create Batch File?", vbYesNo)
Case vbYes
    DoCmd.TransferText acExportFixed, "BFQ_EX_SPEC", "BATCH_FILE_QUERY", "C:\CLBATCHTEST.txt"
    CurrentDb.Execute GetSQL("Batch", True)
    MsgBox ("Batch File Successfuly Created")
End Select
End Sub

What I'm trying to do is have all my query's SQL in a function called GetSQL(). I use it in the above code to update a table after the batch is created. I want to have all the SQL in one place so it's easier to maintain. So is it possible to send something like GetSQL("Batch",False) in the table part of the command?

Public Function GetSQL(category As String, Optional bolMore) As String

Select Case category
Case "Batch"
    If bolMore Then
        GetSQL = "UPDATE Results SET Results.DT_BATCH = DATE() WHERE"
    Else
        GetSQL = "Select * from Results Where "
    End If
    GetSQL = GetSQL + "(([DT_REQUEST] IS NOT NULL AND [DT_BATCH] IS NULL) OR (DateDiff('d',[DT_Notice_Letter],Date())>14 AND [DT_BATCH] IS NULL))"

End Select

End Function

 

[Remou]

You can attach the SQL to a query, for example:

    strSQL = GetSQL(x,y,z)
    'May be version dependent
    If DLookup("Name", "MSysObjects", "Name= 'tmpQry'") <> "" Then
        Set qdf = CurrentDb.QueryDefs("tmpQry")
        qdf.SQL = strSQL
    Else
        Set qdf = CurrentDb.CreateQueryDef("tmpQry", strSQL)
    End If