Run-time error 3075 Syntax error (missing operator) in query expression

[3c4j5d]

I am having difficulty in adding an additional table to a SQL statement. Basically, I want to add that the INSERT should take place if not found in this additional table.

I will attach a file showing the query as it is running successfully now, and then how I am trying to modify it for the additional table and selection criteria where I am receiving the 3075 error. Any help will be greatly appreciated!!

[Rabbit]

Please copy and paste the code into the post and surround it in code tags.

[3c4j5d]

InsertSQL = "INSERT INTO TEMP_DIRECT_RPTS ( MGR_LAN_ID, COWORKER_LAN_ID, COWORKER_LAST_NAME, COWORKER_FIRST_NAME, " _
    & "DATE_INSERTED, USERACCOUNTCONTROL, EMAIL, COMPANY, DEPARTMENT, ACCOUNT_ACTIVE, LOCATION, CITY, STATE, " _
    & "TITLE, EMP_STATUS )" _
    & "SELECT DIRECT_RPTS.MGR_LAN_ID, DIRECT_RPTS.COWORKER_LAN_ID, DIRECT_RPTS.COWORKER_LAST_NAME, " _
    & "DIRECT_RPTS.COWORKER_FIRST_NAME, DIRECT_RPTS.DATE_INSERTED, DIRECT_RPTS.USERACCOUNTCONTROL, " _
    & "DIRECT_RPTS.EMAIL, DIRECT_RPTS.COMPANY, DIRECT_RPTS.DEPARTMENT, DIRECT_RPTS.ACCOUNT_ACTIVE, " _
    & "DIRECT_RPTS.LOCATION, DIRECT_RPTS.CITY, DIRECT_RPTS.STATE, DIRECT_RPTS.TITLE, VP_MAAR_EMP.EMP_STATUS " _
    & "FROM (DIRECT_RPTS LEFT JOIN dbo_VergenceUsernameMap ON DIRECT_RPTS.COWORKER_LAN_ID = dbo_VergenceUsernameMap.ID) " _
    & "INNER JOIN MSAUDIT_REMOVED_USER_LOG ON DIRECT_RPTS.COWORKER_LAN_ID = MSAUDIT_REMOVED_USER_LOG.R_LAN_ID  " _
    & "LEFT JOIN VP_MAAR_EMP ON dbo_VergenceUsernameMap.EmployeeNo = VP_MAAR_EMP.EMPLOYEE " _
    & "WHERE (((DIRECT_RPTS.MGR_LAN_ID)='" & User & "') AND ((VP_MAAR_EMP.EMP_STATUS) Not In ('TF','TP','SN'))) " _
    & "AND DIRECT_RPTS.COWORKER_LAN_ID NOT IN (SELECT R_LAN_ID FROM MSAUDIT_REMOVED_USER_LOG)"

[Rabbit]

So that's the code that works right? Show us the code that doesn't work.

[3c4j5d]

No, sorry.....here is the code that works. What I entered above produces the 3075 error.

InsertSQL = "INSERT INTO TEMP_DIRECT_RPTS ( MGR_LAN_ID, COWORKER_LAN_ID, COWORKER_LAST_NAME, COWORKER_FIRST_NAME, " _
    & "DATE_INSERTED, USERACCOUNTCONTROL, EMAIL, COMPANY, DEPARTMENT, ACCOUNT_ACTIVE, LOCATION, CITY, STATE, " _
    & "TITLE, EMP_STATUS )" _
    & "SELECT DIRECT_RPTS.MGR_LAN_ID, DIRECT_RPTS.COWORKER_LAN_ID, DIRECT_RPTS.COWORKER_LAST_NAME, " _
    & "DIRECT_RPTS.COWORKER_FIRST_NAME, DIRECT_RPTS.DATE_INSERTED, DIRECT_RPTS.USERACCOUNTCONTROL, " _
    & "DIRECT_RPTS.EMAIL, DIRECT_RPTS.COMPANY, DIRECT_RPTS.DEPARTMENT, DIRECT_RPTS.ACCOUNT_ACTIVE, " _
    & "DIRECT_RPTS.LOCATION, DIRECT_RPTS.CITY, DIRECT_RPTS.STATE, DIRECT_RPTS.TITLE, VP_MAAR_EMP.EMP_STATUS " _
    & "FROM (DIRECT_RPTS LEFT JOIN dbo_VergenceUsernameMap ON DIRECT_RPTS.COWORKER_LAN_ID = dbo_VergenceUsernameMap.ID) " _
    & "LEFT JOIN VP_MAAR_EMP ON dbo_VergenceUsernameMap.EmployeeNo = VP_MAAR_EMP.EMPLOYEE " _
    & "WHERE (((DIRECT_RPTS.MGR_LAN_ID)='" & User & "') AND ((VP_MAAR_EMP.EMP_STATUS) Not In ('TF','TP','SN')))"

[NeoPa]

You cannot use an INNER JOIN onto a group of tables that include an OUTER JOIN (LEFT JOIN is an OUTER JOIN).

Your line #9 of post #3 is therefore invalid. Consider joining all the INNER JOIN tables together first (in parentheses) then joining the rest as LEFT JOINs to the resultant group of those.

[3c4j5d]

Thank you both for your replies. I did as you suggested, and now am passed the 3075 error. On to the next challenge. Thank you!

[NeoPa]

Pleased to hear it :-)