SQL query - syntax error (missing operator) in query expression

Question

I would like to seek some help in debugging this SQL query. I would like to join 3 tables/sheets as I am creating this in Excel VBA.

SELECT
    [A-TFN], [Title], [First Name], [Middle Name], [Last Name], [Gender],
    [Date of Birth], [Address 1], [Address 2], [City], [Postal Code], [State],
    [Employment Date], [Benefit Base Salary], [On Plan?]
FROM [Report 1$A9:P9756] e
INNER JOIN [Report 2$A11:C9761] c
    ON c.[Home NUM] = e.[Home NUM]
INNER JOIN [Report 3$A3:B6682] i
    ON i.[Employee Id] = e.[Home NUM]
WHERE (e.[Home NUM] LIKE '%123123123%') OR (e.[Host NUM] LIKE '%123123123%');

Set obj_res = obj_con.Execute(str_sqlquery)

It works fine when joining 2 tables but when I added the INNER JOIN for Report 3, I get the syntax error (missing operator) in query expression.

It displays error in this part

c.[Home NUM] = e.[Home NUM] INNER JOIN [Report 3$A3:B6682] i ON i.[Employee Id] = e.[Home NUM]

Thank you.

Tim Biegeleisen Tim Biegeleisen · Accepted Answer · 2018-10-09T03:36:45

In Access SQL syntax, you need to place the first inner join in parentheses, like this:

SELECT
    [A-TFN], [Title], [First Name], [Middle Name], [Last Name], [Gender],
    [Date of Birth], [Address 1], [Address 2], [City], [Postal Code], [State],
    [Employment Date], [Benefit Base Salary], [On Plan?]
FROM
(
    [Report 1$A9:P9756] e
    INNER JOIN [Report 2$A11:C9761] c
        ON c.[Home NUM] = e.[Home NUM]
)
INNER JOIN [Report 3$A3:B6682] i
    ON i.[Employee Id] = e.[Home NUM]
WHERE (e.[Home NUM] LIKE '%123123123%') OR (e.[Host NUM] LIKE '%123123123%');

SQL query - syntax error (missing operator) in query expression

1 Answers