CODEWITHSUNDEEP
sqlms-access
Ray Harlequin
Ray Harlequin

Reputation: 43

IIF statement with multiple conditions

Consent: 

IIf([consents].[decision]=1,"grant",
    IIf([consents].[allowbreaktheglass]=0,"deny""Default"),
    IIf([consents].[AllowBreakTheGlass]=1 AND [consents].[Decision]=0,"emergency only"))

IIF statement is returning an error:

The expression you entered has a function containing the wrong number of arguments

Criteria for consents are Grant, Deny and Emergency only.

Upvotes: 3

Views: 48346

Answers (3)

HansUp
HansUp

Reputation: 97131

See whether you find Switch easier than nested IIf statements.

Open a new query in the Access query designer, switch to SQL View, and paste in this statement ...

SELECT
    Switch(
            c.[decision]=1, 'grant',
            c.[allowbreaktheglass]=0, 'deny',
            c.[AllowBreakTheGlass]=1 AND c.[Decision]=0, 'emergency only',
            True, 'Default'
          ) AS Consent
FROM consents AS c;

Switch operates on expression/value pairs. It returns the value from the first pair whose expression evaluates as True, and ignores the remaining pairs regardless of whether or not they would be True.

Not sure I understood the logic for what you wanted, but that first pair would return 'grant' if [decision]=1. If not, it would examine the next pair. And so on. The last pair has True as its expression, so when none of the first 3 pairs are matched, the function will return 'Default'.

Upvotes: 1

ccarpenter32
ccarpenter32

Reputation: 1077

It's not 100% clear what you are trying to accomplish with your Nested IIF statement, however you are simply missing one failure argument, the following may be what you want:

Added 'Error?' for the last failure argument, as well as moved the 'Default' into the failure of the first clause. 

Consent: 
    IIF([consents].[decision] = 1, 'grant',
        IIF([consents].[allowbreaktheglass] = 0, 'deny',
            IIF([consents].[AllowBreakTheGlass] = 1 AND [consents].[Decision] = 0,
                'emergency only', 'Error?')) 'Default')

Upvotes: 3

Kashif Qureshi
Kashif Qureshi

Reputation: 1490

Syntax for IIF statement is: IIf ( expr , truepart , falsepart ) You are missing falsepart in following 2:

Try Changing: IIf([consents].[allowbreaktheglass]=0,"deny""Default")

to: IIf([consents].[allowbreaktheglass]=0,"deny","Default")

and: IIf([consents].[AllowBreakTheGlass]=1 AND [consents].[Decision]=0,"emergency only")

to: IIf([consents].[AllowBreakTheGlass]=1 AND [consents].[Decision]=0,"emergency only","")

for more info visit: https://support.office.com/en-us/article/iif-function-32436ecf-c629-48a3-9900-647539c764e3

Upvotes: 2

Related Questions