Lengthy SQL string producing error - Why?

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;"
 
C

Chris O'C via AccessMonster.com

You forgot a few spaces in the string. This should work better for you if
word wrap in the browser doesn't meddle with it.

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;"

Chris
 
C

Chris O'C via AccessMonster.com

Looks like the browser is meddling with the string and breaking it up right
after the dot operator between table name and column name on a few of them.

What I did was add a space before FROM, a space before WHERE and a space
before ORDER. Adjust your original string the same way and your sql should
work.

Chris
 
A

Allen Browne

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
 
W

WiseMonkey

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....
 
Top