CODEWITHSUNDEEP
sqlvbams-access-2007
Tolure
Tolure

Reputation: 879

Another Syntax error (missing operator) in query expression

I need a little help developing my SQL query. My goal is to remove an entry. My condition is two tables away. I've gotten this far but I can't seem to find where the other mistake 

con.Execute "DELETE FROM Expenses INNER JOIN Agreements ON Agreements.AgreementsID = 
             Expenses.AgreementID AND INNER JOIN Audits 
             ON Audits.AuditID = Agreements.AuditID WHERE Audits.Share = True"

I'm using access 2007 and my con variable is 

con.Open _
"Driver={Microsoft Access Driver (*.mdb, *.accdb)};" & _
"Dbq=" & Loc & ";"

Upvotes: 1

Views: 916

Answers (1)

HaveNoDisplayName
HaveNoDisplayName

Reputation: 8497

Try This, You are using AND before INNER JOIN, remove that only.

DELETE DISTINCTROW Expenses.* 
  FROM Expenses 
           INNER JOIN Agreements ON Agreements.AgreementsID = Expenses.AgreementID 
           INNER JOIN Audits ON Audits.AuditID = Agreements.AuditID 
  WHERE Audits.Share = True

[EDIT Query]

  DELETE Expenses.* 
  FROM Expenses 
  WHERE Expenses.AgreementID IN (
                         SELECT Agreements.AgreementID FROM Agreements 
                         INNER JOIN Audits ON Audits.AuditID = Agreements.AuditID
                         WHERE Audits.Share = True
                        )

OP get error:- Too few parameters Expected 2.

To handle this, This error may be obtained because the any column names being selected have special characters in it. If there are special characters in the column names of the database, the name should be surrounded with brackets in the SQL query. So if you have any column name which have special char value , then try [ColumnName]

Upvotes: 1

Related Questions