missing operator but dont know where

[Stuartp]

I am trying to write a query in visual basic that will select records from a table where two variable conditions are both met, and which will then insert these records into another table. The query I have written is:

strSQL = "INSERT INTO [stocksearch] ([CustomerID], [ProductCode], [Quantity]) " & _
"SELECT * " & _
"FROM [stock] " & _
"WHERE [Stock].[CustomerID] = " & custSearch & " and [Stock].[ProductCode] = " & prodSearch & " "
Set dbfSalesdata = DBEngine.Workspaces(0).OpenDatabase(SALESDATA_PATH)
dbfSalesdata.Execute (strSQL)

As far as I can see this is identical to other successful queries I have written, and yet it produces a "syntax error missing operator" error. I am guesssing it is the variables (custSearch and prodSearch) that are causing the problems, but I have tried every combination of brackets, commas, quotes etc that I can think of and nothing makes it right.

Please can someone tell me what the error is?

Thanks.

Stuart

 

[xascendent]

i know I use ' between by varables. such as

"select * from tablename where '" & searchData & "'"

might help.. may not.

 

[Stuartp]

Thanks, but this hasnt worked... at best I just get other errors like "expected expression" and stuff...

 

[woyler]

Try:
strSQL = "INSERT INTO [stocksearch] ([CustomerID], [ProductCode], [Quantity]) " & _
"SELECT YourInsertField1,YourInsertField2 " & _
"FROM [stock] " & _
"WHERE [Stock].[CustomerID] = " & custSearch & " and [Stock].[ProductCode] = " & prodSearch & " "
Set dbfSalesdata = DBEngine.Workspaces(0).OpenDatabase(SALESDATA_PATH)
dbfSalesdata.Execute (strSQL)

 

[woyler]

sorry, submit on accident. Specify the fields you want to insert

 

[Stuartp]

thanks woyler. i also had another hunt round my other vb resources and this method also works:

where [stock].[CustomerID] = """ & custSearch & """ "

why it needs so many quotes is beyond me but that seems to be the wasy to do it!

stuart

 

[woyler]

Try:
where [stock].[CustomerID] ='" & custSearch & "'"
That is assuming that custsearch is as string.
if it were a numeric value:
where [stock].[CustomerID] =" & custSearch & "

When they are resloved they will read
where [stock].[CustomerID] = '10' or
where [stock].[CustomerID] = 10 respectively