Sometimes the TransferText function will not give you the specific text file format you need. This example code demonstrates creating a text file that includes a header record and a footer record. The concepts can be extended to other specific format requirements as well.

Built in A97, and uses DAO. Should work in later versions fince.
--------------------------------------------------------------------------------
Public Sub WriteTestB()
' this function is an example of exporting a query's output to a text file using
' Print # to write to the file line by line
'
' note that since it uses DAO methods a reference to DAO must be set in Tools/References

Dim rs As Recordset ' Recordset to access the query data
Dim intRecordCount As Integer ' used to count records

'open the recordset to get the query data
Set rs = CurrentDb.OpenRecordset("qryCustomerSummary")

' open the text file for output
Open "c:\temp\outdata.txt" For Output As #1

' print a header with the current date
Print #1, "HDRCstDta " & Format(Date, "yyyymmdd")

intRecordCount = 0

' loop through all records in the query
Do Until rs.EOF
' write a record to the text file, formatted as follows:
' Customer ID, padded on the right with spaces to eight characters,
' ContacteName, padded on the right with spaces to 25 characters
' (left is used to make sure it doesn't exceed 25)
' TotalOderValue, 8 digits before decimal and two after, zero filled
Print #1, Format(rs!CustomerID, "!@@@@@@@@"); _
Format(Left(rs!ContactName, 25), "!@@@@@@@@@@@@@@@@@@@@@@@@@"); _
Format(rs!TotalOrderValue, "00000000.00")

intRecordCount = intRecordCount + 1
' move to next record in query output
rs.MoveNext
Loop

' print a trailer with the record count
Print #1, "TLRCstDtaRec=" & Format(intRecordCount, "000000")

' close the text file
Close #1

'close the recordset
rs.Close
Set rs = Nothing

End Sub

--------------------------------------------------------------------------------