Syntax error (missing operator) in query expression VBA while multiple inner join

Question

I am getting below Syntax error (missing operator)

Please find my below VBA code.

Sub macro()

sql_string = "SELECT [Sheet2$].[Sr], [no], [Code], [Sheet3$].[Srr], [Family], [nos], [Sheet1$].[Sr], [LongName]" & _
" FROM [Sheet3$], [Sheet2$], [Sheet1$] INNER JOIN [Sheet2$] ON [Sheet2$].[Sr]=[Sheet3$].[Srr]" & _
" INNER JOIN [Sheet2$] ON [Sheet2$].[no]=[Sheet3$].[nos]"

sq = SQL_query(sql_string)

end sub

Function SQL_query(ByRef sql_string As Variant)

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

strFile = ThisWorkbook.FullName
strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFile _
& ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open strCon

strSQL = sql_string

rs.Open strSQL, cn

Sheet5.Range("A21").CopyFromRecordset rs
End Function

Please find the below tables.

Table1-

Table2-

Table3-

Please guide where i am missing in the code.

which line does the code break on? Is it rs.Open strSQL, cn ? — ashleedawg
– ashleedawg, Commented Oct 31, 2017 at 13:32
In Access, ACE, I believe you can only join two tables at a time. I've tried before and ended up frustrated (SQL Server, this would work). You'd have to restructure it like this answer, here — Jimmy Smith
– Jimmy Smith, Commented Oct 31, 2017 at 14:08
You can join multiple tables, I don't think that's the issue — ashleedawg
– ashleedawg, Commented Oct 31, 2017 at 14:25

sagar · Accepted Answer · 2017-11-01 07:45:52Z

2

Thank all for the help, As per your guidance.

the below syntax are working for me for multiple inner join.

sql_string = "SELECT [Sheet2$].[Sr], [no], [Code], [Sheet3$]." & _
    "[nos], [Family], [Sheet1$].[LongName]" & _
    " FROM (([Sheet2$] INNER JOIN [Sheet3$] ON [Sheet2$].[Sr]=[Sheet3$].[Srr])" & _
    " INNER JOIN [Sheet1$] ON [Sheet1$].[Sr]=[Sheet3$].[Srr])"

answered Nov 1, 2017 at 7:45

sagar

39711 silver badges26 bronze badges

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

Comments

ashleedawg · Accepted Answer · 2017-10-31 14:57:54Z

A good way to troubleshoot messy SQL queries in VBA is to break them up, since SQL will ignore all the extra spaces & line breaks but we will be able to better understand it, and find problems.

So, we could split your line with the query up like:

sql_string = "
    SELECT
        [Sheet2$].[Sr],
        [no],
        [Code], 
        [Sheet3$].[Srr], 
        [Family], 
        [nos], 
        [Sheet1$].[Sr], 
        [LongName]
    FROM [Sheet3$], [Sheet2$], [Sheet1$] 
    INNER JOIN [Sheet2$] 
        ON [Sheet2$].[Sr]=[Sheet3$].[Srr]
    INNER JOIN [Sheet2$] 
        ON [Sheet2$].[no]=[Sheet3$].[nos]"

and now we can see a few mix-ups in your joins, which I further tidied up below, as well as specified table names on all the field. There's a couple issues, for example You can't join 3 tables to 1 table, but you can do a join between two tables, and then a join between "that" and the third table.

sql_string = "
    SELECT
        [Sheet2$].[Sr],
        [Sheet2$].[no],
        [Sheet2$].[Code], 

        [Sheet3$].[Srr], 
        [Sheet3$].[nos], 
        [Sheet3$].[Family], 


        [Sheet1$].[Sr], 
        [Sheet1$].[LongName]

    FROM [Sheet2$]
    INNER JOIN [Sheet3$]
        ON [Sheet2$].[Sr]=[Sheet3$].[Srr]

    INNER JOIN [Sheet1$] 
        ON [Sheet1$].[Sr]=[Sheet3$].[Srr]"

Paste that in to replace your sql_string = "SELECT [Sheet2$].... line (as is, ignore how long it is now)

Let me know how it goes!

Here's a link to more info on nested `Inner Joins` from Microsoft Support

You can also link several ON clauses in a JOIN statement, using the following syntax:
SELECT fields
FROM table1 INNER JOIN table2
ON table1.field1compoprtable2.field1 AND
ON table1.field2compoprtable2.field2) OR
ON table1.field3compoprtable2.field3)];
You can also nest JOIN statements using the following syntax:
SELECT fields FROM table1 INNER JOIN (table2 INNER JOIN [( ]table3
[INNER JOIN [( ]tablex [INNER JOIN ...)] ON
table3.field3compoprtablex.fieldx)] ON
table2.field2compoprtable3.field3) ON
 table1.field1compoprtable2.field2;
A LEFT JOIN or a RIGHT JOIN may be nested inside an INNER JOIN, but an INNER JOIN may not be nested inside a LEFT JOIN or a RIGHT JOIN. (That last part shouldn't apply in this case since you've got straight nested INNER JOINs now.)

Collectives™ on Stack Overflow

Syntax error (missing operator) in query expression VBA while multiple inner join

2 Answers 2

Comments

Here's a link to more info on nested `Inner Joins` from Microsoft Support

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Here's a link to more info on nested Inner Joins from Microsoft Support

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related

Here's a link to more info on nested `Inner Joins` from Microsoft Support