Lengthy SQL string producing error - Why?

  • Thread starter Thread starter WiseMonkey
  • Start date Start date
W

WiseMonkey

Can anyone help me with why this SQL string is throwing up a "Missing
Operator" error when run from VBA?

(I bet it's something simple right?)

strSQL = "SELECT tblVendors.vID, tblVendors.vName, tblCommodityProjs.BidNum,
tblCommodityProjs.BidTitle,tblVendors.vHub, tblVendors.vNonMinority," & _
"tblVendors.vHubAFAM, tblVendors.vHubAmInd,
tblVendors.vHubAsAm, tblVendors.vHubHisLatAm, tblVendors.vHubW,
tblVendors.vHubD," & _
"tblVendors.vHubDisad, tblVendors.vHubUnd,
tblVendors.vHubUnk, tblVendors.vDOBE, tblVendors.vNPWCFTB" & _
"FROM tblCommodityProjs " & _
"LEFT JOIN (tblCommTrans LEFT JOIN tblVendors ON
tblCommTrans.vName = tblVendors.vID) ON tblCommodityProjs.bID =
tblCommTrans.sBid" & _
"WHERE (((tblVendors.vHubAFAM) = True))" & _
"ORDER BY tblVendors.vName;"
 
Try INNER JOINs.

The fields on the outer side of a join can be Null if there is no match.
Your criteria eliminates the Nulls anyway (if vHubAFAM is true, then it's
not null), so the inner join should give you the results you want.

JET (the database engine in Access) does not cope with Nulls in Yes/No
fields, so this could be the cause of the nonsense error you received. More
info and examples in:
Outer join queries fail on Yes/No fields
at:
http://allenbrowne.com/bug-14.html
 
Thanks for the responses guys.

Chris's solution solved my immediate problem but thanks to Allen's input I
clearly need to consider more than just getting my code to work....
 
Back
Top