Syntax error (missing operator) in query expression VBA

Question

I'm getting an error message when I want to run my query (syntax error (missing operator) in query expression). I don't know where the issue can be.

I have created a macro (VBA) which should show all manager level in my company.

The columns should be like this:

Top manager, directors, managers, team leaders, supervisors, employees.

Sub TEST()
    Application.ScreenUpdating = False

    Dim cnStr                 As String
    Dim rs                    As ADODB.Recordset
    Dim query                 As String
    Dim fileName              As String
    pom4 = "';"

    fileName = "C:\sciezka\DEU.xlsx"

    cnStr = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
            "Data Source=" & fileName & ";" & _
            "Extended Properties=Excel 12.0"


    query = "SELECT L1.[DEU1$].[Supervisor ID] AS N0, L1.[DEU1$].[Emplid] AS N1, L2.[DEU1$].[Emplid] AS N2, L3.[DEU1$].[Emplid] AS N3, " & _
            "L4.[DEU1$].[Emplid] AS N4, L5.[DEU1$].[Emplid] AS N5, L6.[DEU1$].[Emplid] AS N6, L7.[DEU1$].[Emplid] AS N7 FROM [DEU1$] L1 " & _
            "LEFT JOIN [DEU1$] L2 ON (L1.[Emplid]=L2.[Supervisor ID]) LEFT JOIN [DEU1$] L3 ON (L2.[Emplid]=L3.[Supervisor ID]) " & _
            "LEFT JOIN [DEU1$] L4 ON (L3.[Emplid]=L4.[Supervisor ID]) LEFT JOIN [DEU1$] L5 ON (L4.[Emplid]=L5.[Supervisor ID]) " & _
            "LEFT JOIN [DEU1$] L6 ON (L5.[Emplid]=L6.[Supervisor ID]) LEFT JOIN [DEU1$] L7 ON (L6.[Emplid]=L7.[Supervisor ID]) WHERE L1.[Supervisor ID] LIKE '%15981%';"

    Set rs = New ADODB.Recordset

    rs.Open query, cnStr, adOpenUnspecified, adLockUnspecified

    Cells.Clear

    Dim cell As Range, i      As Long

    With Range("A16").CurrentRegion
        For i = 0 To rs.Fields.Count - 1
            .Cells(1, i + 1).Value = rs.Fields(i).Name
        Next i
        Range("A17").CopyFromRecordset rs
        .Cells.Select
        .EntireColumn.AutoFit
    End With

    rs.Close
    Application.ScreenUpdating = True
End Sub

You can't use L1 etc as both a field alias and a table alias... — Rory
Hi, I have changed the column names like: SELECT L1.[DEU1$].[Supervisor ID] AS N0, L1.[DEU1$].[Emplid] AS N1 and so on but still the same error mesage — MIREK
Please update the code in the question with what you now have. — Rory

Rory Rory · Accepted Answer · 2015-05-13T14:06:52

I think you just need to add some parentheses as well as not using both the alias name and the actual table name:

query = "SELECT L1.[Supervisor ID] AS N0, L1.[Emplid] AS N1, L2.[Emplid] AS N2, L3.[Emplid] AS N3, " & _
        "L4.[Emplid] AS N4, L5.[Emplid] AS N5, L6.[Emplid] AS N6, L7.[Emplid] AS N7 FROM ((((([DEU1$] L1 " & _
        "LEFT JOIN [DEU1$] L2 ON L1.[Emplid] = L2.[Supervisor ID]) " & _
        "LEFT JOIN [DEU1$] L3 ON L2.[Emplid] = L3.[Supervisor ID]) " & _
        "LEFT JOIN [DEU1$] L4 ON L3.[Emplid] = L4.[Supervisor ID]) " & _
        "LEFT JOIN [DEU1$] L5 ON L4.[Emplid] = L5.[Supervisor ID]) " & _
        "LEFT JOIN [DEU1$] L6 ON L5.[Emplid] = L6.[Supervisor ID]) " & _
        "LEFT JOIN [DEU1$] L7 ON L6.[Emplid]=L7.[Supervisor ID];"

Syntax error (missing operator) in query expression VBA

1 Answers