Link to home
Start Free TrialLog in
Avatar of Sandra Smith
Sandra SmithFlag for United States of America

asked on

Nest Iif statements in ACCESS 2003 query

I have a query behind a form that needs to have nested iif statements, but I can't seem to get the syntax correct.  Attached is the first leve and it works.  But I need to also add Iif(tblStatusResults.period_range_type = 'Unknown', "NO PERIOD RANGE TYPE-MUST ADD!", "")  in the statement, but I keep getting the error wrong arguments.  
ExceptionComment: IIf(tblStatusResults.ProblemGroup='ExpectedRun',IIf(IsNull(tblStatusResults.AsOfDate),'Never Run','ExpectedDate: ' & tblStatusResults.ExpectedDate),'')

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I'm assuming you want it as the final iif:

IIf(tblStatusResults.ProblemGroup='ExpectedRun',IIf(IsNull(tblStatusResults.AsOfDate),'Never Run','ExpectedDate: ' & tblStatusResults.ExpectedDate),Iif(tblStatusResults.period_range_type = 'Unknown', "NO PERIOD RANGE TYPE-MUST ADD!", "", ''))

It's all about where you leave the commas and closing parentheses.

-R
Where do you want to check the new IIF condition? check if this is what you are looking for.

IIF(tblStatusResults.ProblemGroup = 'ExpectedRun',
           IIF(ISNULL(tblStatusResults.AsOfDate),'Never Run',
               'ExpectedDate: ' & tblStatusResults.ExpectedDate),
           IIF(tblStatusResults.period_range_type = 'Unknown',
               'NO PERIOD RANGE TYPE-MUST ADD!',''))
Avatar of Sandra Smith

ASKER

Swtich works the best as I also need to add other conditions that are mutually exclusive.
Thank you, this was even better than the route I was going.
ssmith94015,

Glad to help!  If you have not already done so, I would really appreciate it if you could please return to my article
https://www.experts-exchange.com/Microsoft/Development/MS_Access/A_3556-Using-the-Switch-Function-in-Microsoft-Access.html
and click 'Yes' for the 'Was this helpful?' voting.

Patrick
Patrick, done.