expor to text file

[fule12]

hi to all
I have code for export query to text file:
Dim db As Database, rst As Recordset, SQL As String
Dim a As Integer, AccCode, OrderNo, Myfile As String
Set db = CurrentDb
Myfile = "C:\test.txt"
SQL = "SELECT Employees.FirstName, Employees.LastName, Employees.DepartmentID FROM Employees WHERE (((Employees.DepartmentID)>1));"
Set rst = db.OpenRecordset(SQL)
rst.MoveLast
rst.MoveFirst
Open Myfile For Output As #1

For a = 1 To rst.RecordCount
AccCode = Trim(rst!FirstName) & Space(10 - Len(Trim(rst!FirstName)))
OrderNo = Trim(rst!LastName) & Space(25 - Len(Trim(rst!LastName)))
Print #1, AccCode & OrderNo
rst.MoveNext
Next
Close #1
rst.Close
db.Close

and i create form with 1 button so when u click on that button is nead to make text file. but is give me run-time '13'error :
Type Mismatch
on line :Set rst = db.OpenRecordset(SQL)

Maybe i make code on wrong place?
and how to make header and footer fot text file?

Thanks





Fule

 

[smedvid]

Perhaps another approach to consider is using TransferText or Output To options. Certainly less coding to export query results.

Other ideas:
1. Try to remove the ";", I don't think it is required.
2. Try to run the query to see if it actaully works.
3. Try to insert the actauly SQL into the OpenRecordSet().

Here is what I do:

call CreateExportQry(True)

lcExportFile_NP = lcExportPath & "K_Imp_NoProd_" & Format(Now(), "mmddyyyyhhnnss&quot & ".txt" 'Generate Export File Name
DoCmd.TransferText acExportDelim, "Export_Spec", "Temp_Export_NP", lcExportFile_NP 'Use Spec to suppress Text Qualifier on Export.


Public Function Create_Export_Query(Dim3_Flag As Boolean)
Dim intICnt As Integer
Dim lcStrItems As String
Dim StrItems As String
Dim lcQryStr As String
Dim lc_Query As QueryDef
Dim lcQueryName As String
Dim lcExportPath As String
Dim lcExportFile As String
Dim thisDBx As DAO.Database
Dim ln_Select_Count As Integer

If Dim3_Flag = True Then
lcQueryName = "Temp_Export_P"
Else
lcQueryName = "Temp_Export_NP"
End If
Set thisDBx = CurrentDb

'Create Export Query
'-------------------
For Each lc_Query In thisDBx.QueryDefs
If Trim(lc_Query.Name) = Trim(lcQueryName) Then
thisDBx.QueryDefs.Delete (lcQueryName)
End If
Next lc_Query
thisDBx.QueryDefs.Refresh
lcQryStr = "SELECT tbl_PRIMARY_DATA.FIELD_REFERENCE & '}' & 'A' & Format([MONTH_YEAR],'yymm') & '}' & " & _
"tbl_PRIMARY_DATA.BUSINESS_UNIT & '}' & tbl_PRIMARY_DATA.FIELD_DIM3 & '}}N}' & " & _
"tbl_PRIMARY_DATA.FIELD_VALUE AS Export_Data " & _
"FROM tbl_PRIMARY_DATA " & _
"WHERE tbl_PRIMARY_DATA.MONTH_YEAR = #" & Me.MONTH_YEAR & "#"
If Dim3_Flag = True Then
lcQryStr = lcQryStr & " AND ((tbl_PRIMARY_DATA.FIELD_DIM3) Is Not Null)"
Else
lcQryStr = lcQryStr & " AND ((tbl_PRIMARY_DATA.FIELD_DIM3) Is Null)"
End If

If chkAll.Value = -1 Then 'Select All
'Nothing to do here!
Else
'Need to loop through to find only those Business Units Selected by User and build Where clause
ln_Select_Count = 0 'Initializing the counter
For intICnt = 0 To Me.lst_Bus_Units.ListCount - 1 'This loops from first entry in the list
If Me.lst_Bus_Units.Selected(intICnt) Then
ln_Select_Count = ln_Select_Count + 1
If ln_Select_Count = Me.lst_Bus_Units.ItemsSelected.Count Then
StrItems = StrItems & "'" & Me.lst_Bus_Units.Column(0, intICnt) & "'"
lcStrItems = lcStrItems & Me.lst_Bus_Units.Column(0, intICnt)
Else
StrItems = StrItems & "'" & Me.lst_Bus_Units.Column(0, intICnt) & "'" & ","
lcStrItems = lcStrItems & Me.lst_Bus_Units.Column(0, intICnt) & ","
End If
End If
Next intICnt
lcQryStr = lcQryStr & " AND (BUSINESS_UNIT IN (" & StrItems & &quot)"
End If

'Create Query in Database
'------------------------
Set lc_Query = thisDBx.CreateQueryDef(lcQueryName, lcQryStr)
thisDBx.QueryDefs.Refresh
thisDBx.Close
Exit Function
End Function
Steve Medvid
"IT Consultant & Web Master"

http://users.erols.com/medvid/

 

[B827]

Are you sure the Department ID is a numeric field, if it is text try
SQL = "SELECT Employees.FirstName, Employees.LastName, Employees.DepartmentID FROM Employees WHERE (((Employees.DepartmentID)>'1'));"
Sandy

 

[smedvid]

Try to remove the entire Where clause to see if that is the problem area also... Steve Medvid
"IT Consultant & Web Master"

http://users.erols.com/medvid/