Update Using IIF statement Error

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Update Using IIF statement Error

Author

Topic

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2011-09-13 : 11:47:46

Here is my exact code with the conditions to see if anyone can spot mistakes. Trying to update a column with conditions dependant on another table that is linked to the table to be updated.The error is:


Syntax error (missing operator) in query expression 'IIf(D.T = "Sgt.", "Sgt.", "Trainer")
FROM
(SELECT C1.ReferredBy AS Ref, C3.Title AS T, C3.ContactType as CT
                ((FROM tblCompanies C1 
                INNER JOIN companycontacts C2 ON C1.CompanyID = C2.CompanyID)
                INNER JO'

Update query below:


UPDATE D
SET D.Ref = IIf(D.T = "Sgt.", "Sgt.", "Trainer")
FROM
(SELECT C1.ReferredBy AS Ref, C3.Title AS T, C3.ContactType as CT
                ((FROM tblCompanies C1 
                INNER JOIN companycontacts C2 ON C1.CompanyID = C2.CompanyID)
                INNER JOIN tblContacts C3 ON C2.ContactID = C3.ContactID)
                WHERE C3.Title = "Sgt." OR C3.ContactType = "Trainer") AS D
WHERE D.T = "Sgt." OR D.CT = "Trainer";

Does the use of single or double quotes matter? It doesn't seem to make any difference to the error.

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2011-09-14 : 05:47:57

Ok in the spirit of helping others the access update statement does not use a FROM clause in it. This code below now works:

UPDATE tblContacts AS C1 INNER JOIN (tblCompanies AS C2 INNER JOIN CompanyContacts AS C3 ON C2.CompanyID=C3.CompanyID) ON C1.ContactID=C3.ContactID SET C2.ReferredBy = SWITCH(C1.Title='Condition1','Condition1',C1.ContactType='Condition2','Condition2')
WHERE (((C1.Title)='Condition1')) OR (((C1.ContactType)='Condition2'));

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources