Using IIF in Ms Access

Question

User-871891371 posted

Can anyone please tell me what is wrong with this expression.

"SELECT UserID, Username, IIF (IsNull(SELECT Sharewith FROM SharedDocuments WHERE DocumentCode = 'TEXT' AND DocumentAuto=3 AND ShareWith = UserID), True, False) AS IsShared

FROM Users

WHERE UserID <>'Test'

ORDER BY UserName, IsShared;"

Thank You

ATE 

Answer 1

User-1005100348 posted

Try:

IIF (Exists(SELECT Sharewith FROM SharedDocuments 
            WHERE DocumentCode = 'TEXT' AND 
                  DocumentAuto=3 AND 
                  ShareWith = UserID), False, True) as IsShared

Hope this helps. 

Answer 2

User-1716253493 posted

Joint the tables with show all [Users] rows

IIF (SharedDocuments.DocumentCode = 'TEXT' AND SharedDocuments.DocumentAuto=3, True, False) AS IsShared

Answer 3

User3866881 posted

IIF (IsNull(SELECT Sharewith FROM SharedDocuments WHERE DocumentCode = 'TEXT' AND DocumentAuto=3 AND ShareWith = UserID), True, False) AS IsShared

Hi,

Please quote the As…… into the pair of "()":

SELECT UserID, Username, (IIF(IsNull(SELECT Sharewith FROM SharedDocuments WHERE DocumentCode = 'TEXT' AND DocumentAuto=3 AND ShareWith = UserID), True, False)) AS IsShared

Answer 4

User-871891371 posted

Thank you all for your time.