EXCEL VBA/ADODB Connection / LEFT JOIN 3 tables SQL - Syntax Error

Question

I have a workbook set up with 3 separate tabs/tables, and I am trying to do a 3 way LEFT JOIN query on this workbook (using SQL via the commandtext box on the ADODB connection in Excel 03), and I get a Syntax Error (Missing Operator) msgbox:

SELECT B.[Business], A.[book], C.[bus_area] 

FROM [Bon$] as A LEFT JOIN [DM$] as B ON (A.[book] = B.[SystemBookName] ) 
LEFT JOIN [BA$] as C ON B.[SystemBookName] = C.[portfolio_name]  

WHERE A.[area] NOT LIKE "%TEST%"

I know this is probably due to some syntax error/ or where I place the paranthesis, but I tried multiple combinations and it does not seem to work. Any ideas?

Many thanks

Thanks juergen - I have but still got an error. updated my post though. — DC1
– DC1, Commented Sep 23, 2014 at 11:06

DC1 · Accepted Answer · 2014-09-23 12:35:38Z

2

Ah I figured out why. The code was thrown out because it saw it missing the operator FROM before the second LEFT JOIN. So I put in paranthesis everything after FROM and the second LEFT JOIN and it worked.

SELECT B.[Business], A.[book], C.[bus_area] 

FROM ([Bon$] as A LEFT JOIN [DM$] as B ON A.[book] = B.[SystemBookName] ) 
LEFT JOIN [BA$] as C ON B.[SystemBookName] = C.[portfolio_name]  

WHERE A.[area] NOT LIKE "%TEST%"

answered Sep 23, 2014 at 12:35

DC1

311 silver badge3 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

EXCEL VBA/ADODB Connection / LEFT JOIN 3 tables SQL - Syntax Error

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related