TransferText can export a stored query or a table; cannot use an SQL
statement as the source. You'll need to save the SQL statement as a query
and then do the export. If you don't want to keep the query permanently
after you do the export, use code similar to this to create and then destroy
the query:

EXAMPLE FOR HOW TO CREATE A NEW QUERY IN ORDER TO CONTROL THE QUERY'S
CONTENTS AND NAME, AND TO EXPORT IT TO A TEXT FILE USING
"TRANSFERTEXT" COMMAND.
-------------------------

Dim qdf As DAO.QueryDef, qds As DAO.QueryDef
Dim dbs As DAO.Database
Dim intLoop As Integer
Dim strSQL As String, strQ As String

Set dbs = CurrentDb

' Use next four steps if the new query is being copied from
' an existing query
'Set qds = dbs.QueryDefs("QueryBeingUsedAsTemplate")
'strSQL = qds.SQL
'qds.Close
'Set qds = Nothing

' Use next step if you want to build the SQL string for the new query
'strSQL = "Your SQL statement goes here"

strQ = "NameOfNewQuery"
Set qdf = dbs.CreateQueryDef(strQ, strSQL)
qdf.Close
DoCmd.TransferText acExportDelim, "SpecificationName", qdf.Name, _
   "ExportToFile.txt", True
dbs.QueryDefs.Delete strQ
Set qdf = Nothing
dbs.Close
Set dbs = Nothing