locked
Using IIF in Ms Access RRS feed

  • 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 

    Friday, November 9, 2012 12:43 PM

Answers

  • 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. 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 9, 2012 1:33 PM
  • User-1716253493 posted

    Joint the tables with show all [Users] rows

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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 9, 2012 10:09 PM

All replies

  • 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. 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 9, 2012 1:33 PM
  • User-1716253493 posted

    Joint the tables with show all [Users] rows

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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 9, 2012 10:09 PM
  • 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
    Saturday, November 10, 2012 1:36 AM
  • User-871891371 posted

    Thank you all for your time.

    Monday, November 12, 2012 5:58 AM