By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,482 Members | 2,438 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,482 IT Pros & Developers. It's quick & easy.

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

P: 26
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!!
Attached Files
File Type: txt Error3075addingMSAUDIT_REMOVED_USER_LOG.txt (2.8 KB, 224 views)
Jan 30 '12 #1
Share this Question
Share on Google+
7 Replies


Rabbit
Expert Mod 10K+
P: 12,315
Please copy and paste the code into the post and surround it in code tags.
Jan 30 '12 #2

P: 26
Expand|Select|Wrap|Line Numbers
  1. InsertSQL = "INSERT INTO TEMP_DIRECT_RPTS ( MGR_LAN_ID, COWORKER_LAN_ID, COWORKER_LAST_NAME, COWORKER_FIRST_NAME, " _
  2.     & "DATE_INSERTED, USERACCOUNTCONTROL, EMAIL, COMPANY, DEPARTMENT, ACCOUNT_ACTIVE, LOCATION, CITY, STATE, " _
  3.     & "TITLE, EMP_STATUS )" _
  4.     & "SELECT DIRECT_RPTS.MGR_LAN_ID, DIRECT_RPTS.COWORKER_LAN_ID, DIRECT_RPTS.COWORKER_LAST_NAME, " _
  5.     & "DIRECT_RPTS.COWORKER_FIRST_NAME, DIRECT_RPTS.DATE_INSERTED, DIRECT_RPTS.USERACCOUNTCONTROL, " _
  6.     & "DIRECT_RPTS.EMAIL, DIRECT_RPTS.COMPANY, DIRECT_RPTS.DEPARTMENT, DIRECT_RPTS.ACCOUNT_ACTIVE, " _
  7.     & "DIRECT_RPTS.LOCATION, DIRECT_RPTS.CITY, DIRECT_RPTS.STATE, DIRECT_RPTS.TITLE, VP_MAAR_EMP.EMP_STATUS " _
  8.     & "FROM (DIRECT_RPTS LEFT JOIN dbo_VergenceUsernameMap ON DIRECT_RPTS.COWORKER_LAN_ID = dbo_VergenceUsernameMap.ID) " _
  9.     & "INNER JOIN MSAUDIT_REMOVED_USER_LOG ON DIRECT_RPTS.COWORKER_LAN_ID = MSAUDIT_REMOVED_USER_LOG.R_LAN_ID  " _
  10.     & "LEFT JOIN VP_MAAR_EMP ON dbo_VergenceUsernameMap.EmployeeNo = VP_MAAR_EMP.EMPLOYEE " _
  11.     & "WHERE (((DIRECT_RPTS.MGR_LAN_ID)='" & User & "') AND ((VP_MAAR_EMP.EMP_STATUS) Not In ('TF','TP','SN'))) " _
  12.     & "AND DIRECT_RPTS.COWORKER_LAN_ID NOT IN (SELECT R_LAN_ID FROM MSAUDIT_REMOVED_USER_LOG)"
  13.  
Jan 30 '12 #3

Rabbit
Expert Mod 10K+
P: 12,315
So that's the code that works right? Show us the code that doesn't work.
Jan 30 '12 #4

P: 26
No, sorry.....here is the code that works. What I entered above produces the 3075 error.
Expand|Select|Wrap|Line Numbers
  1.  
  2. InsertSQL = "INSERT INTO TEMP_DIRECT_RPTS ( MGR_LAN_ID, COWORKER_LAN_ID, COWORKER_LAST_NAME, COWORKER_FIRST_NAME, " _
  3.     & "DATE_INSERTED, USERACCOUNTCONTROL, EMAIL, COMPANY, DEPARTMENT, ACCOUNT_ACTIVE, LOCATION, CITY, STATE, " _
  4.     & "TITLE, EMP_STATUS )" _
  5.     & "SELECT DIRECT_RPTS.MGR_LAN_ID, DIRECT_RPTS.COWORKER_LAN_ID, DIRECT_RPTS.COWORKER_LAST_NAME, " _
  6.     & "DIRECT_RPTS.COWORKER_FIRST_NAME, DIRECT_RPTS.DATE_INSERTED, DIRECT_RPTS.USERACCOUNTCONTROL, " _
  7.     & "DIRECT_RPTS.EMAIL, DIRECT_RPTS.COMPANY, DIRECT_RPTS.DEPARTMENT, DIRECT_RPTS.ACCOUNT_ACTIVE, " _
  8.     & "DIRECT_RPTS.LOCATION, DIRECT_RPTS.CITY, DIRECT_RPTS.STATE, DIRECT_RPTS.TITLE, VP_MAAR_EMP.EMP_STATUS " _
  9.     & "FROM (DIRECT_RPTS LEFT JOIN dbo_VergenceUsernameMap ON DIRECT_RPTS.COWORKER_LAN_ID = dbo_VergenceUsernameMap.ID) " _
  10.     & "LEFT JOIN VP_MAAR_EMP ON dbo_VergenceUsernameMap.EmployeeNo = VP_MAAR_EMP.EMPLOYEE " _
  11.     & "WHERE (((DIRECT_RPTS.MGR_LAN_ID)='" & User & "') AND ((VP_MAAR_EMP.EMP_STATUS) Not In ('TF','TP','SN')))"
  12.  
Jan 30 '12 #5

NeoPa
Expert Mod 15k+
P: 31,186
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.
Jan 30 '12 #6

P: 26
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!
Jan 31 '12 #7

NeoPa
Expert Mod 15k+
P: 31,186
Pleased to hear it :-)
Jan 31 '12 #8

Post your reply

Sign in to post your reply or Sign up for a free account.