Search Form Result to excel - Item not found - Where to start

[Billp]

Hi,
I have a search form and have been given the task of getting the searched
filtered records to an excel spreadsheet.
I am getting "Item Not Found In This Collection" and do not know where to
start to find the reason.
I have taken the following and adapted from another working similar
application.
' this is to send the forms results to an excel spreadsheet
Dim dbsCurrent As DAO.Database
Dim qryTest As QueryDef
Dim SQL As String
Dim QueryStringSelect As String
Dim QueryStringFrom As String
Dim QueryStringWhere As String

'output filtered data if some filtering is in place
If IsNull(FilterForm()) = False Then 'filters in place
'build the query strings for the filter

QueryStringSelect = "SELECT
[frmFindWCardCriteria_Search.txtCompanyName,frmFindWCardCriteria_Search.txtCustomerID,frmFindWCardCriteria_Search.Works_Number" _
&
",frmFindWCardCriteria_Search.txtSalesPerson,frmFindWCardCriteria_Search.txtCustomerContact,frmFindWCardCriteria_Search.txtCountry" _
&
",frmFindWCardCriteria_Search.txtOrder_Number,frmFindWCardCriteria_Search.txtInstallation,frmFindWCardCriteria_Search.txtStartDate" _
& ",frmFindWCardCriteria_Search.txtEndDate]"
Debug.Print QueryStringSelect
QueryStringFrom = " FROM Company_WCard_Report_Query"
QueryStringWhere = " WHERE" & FilterForm()

'do the dirty and get it done

Set dbsCurrent = CurrentDb
Set qryTest = dbsCurrent.QueryDefs("WCard_Criteria")
qryTest.SQL = QueryStringSelect & QueryStringFrom & QueryStringWhere

'now send the result to excel
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, ,
"WCard_Criteria", "C:\WCard_Criteria.xls", True
MsgBox "Filtered data exported to root C: directory"
End If

Any help appreciated

 

[PieterLinden via AccessMonster.com]

The thing that jumps out at me is the odd use of the brackets. Take those
out. You _only_ need brackets in a SQL statement if you have spaces in the
name of a source object or field name. I would start there and maybe print
the resulting SQL statement to the immediate window and then copy & paste it
into a query and try to run it. If it runs, then your SQL isn't the problem..