Syntax Problem and a Question on SQL

[rbinder]

Greetings all,

I am having trouble translating a query to SQL. The error message I am getting is;
Run time error 3075
Syntax error (missing operator) in query expression
'tbl_Data_AllocationDetails.TradingName = CompanyB'

The code I am using is;
varRet = [Forms]![frm_View_Company_Allocations]![cmbTradingName]

StrSQL = "INSERT INTO ztbl_Company_Current_Statement ( WineNumber, Allocation ) "
StrSQL = StrSQL & "SELECT tbl_Data_AllocationLineitems.WineNumber, tbl_Data_AllocationLineitems.Amount "
StrSQL = StrSQL & "FROM tbl_Data_AllocationDetails INNER JOIN tbl_Data_AllocationLineitems ON tbl_Data_AllocationDetails.AllocationID = tbl_Data_AllocationLineitems.AllocationID "
StrSQL = StrSQL & "WHERE tbl_Data_AllocationDetails.TradingName =" & varRet
Set db = DBEngine(0)(0)

db.Execute (StrSQL)

I have used brackets, taken bits of the code out and still having the same problem. It is frustrating me.

Obviously I have made a small error in converting the code from the query to here. Any help would be appreciated.

My second question is what is better from a coding standard - accepted programming perpective. To use code to run queries and select data or to use a docmd.openquery "qryname" using stored queries?

rbinder

 

[pbaldy]

Try:

StrSQL = StrSQL & "WHERE tbl_Data_AllocationDetails.TradingName ='" & varRet & "'"

Generally speaking, executing saved queries is more efficient that SQL from VBA.