# Thread: IIF Statement with multiple criteria (Access 97)

1. ## IIF Statement with multiple criteria (Access 97)

Hello!

Hoping that someone can help me with this. I need an Iif statement that will return a 1 if "NO" is found in any of these fields :EL1a, CA1a, RD11a, CD15a, or PF7a. I can create an Iif statement for each one individually, and then another statement that combines all of my single statements, but wanted to check to see if there is a way to combine this initially. This is what I have (too much):
Expr1: IIf([EL1a]="No",1,0)
Expr2: IIf([CA1a]="No",1,0)
Expr3: IIf([CD15a]="No",1,0)
Expr4: IIf([RD11a]="No",1,0)
Expr5: IIf([PF7a]="No",1,0)
Expr6: IIf([Expr1] Or [Expr2] Or [Expr3] Or [Expr4] Or [Expr5]=1,1,0)

Thanks for any assistance that you can provide.

2. ## Re: IIF Statement with multiple criteria (Access 97)

You could use
<code>
-([EL1a]="No" Or [CA1a]="No" Or [CD15a]="No" Or [RD11a]="No" Or [PF7a]="No")
</code>
The result of the expression between parentheses is either True = -1 or False = 0. The minus sign changes -1 to 1.

Note: if EL1a etc. were Yes/No fields, you could use
<code>
1-([EL1a] And [CA1a] And [CD15a] And [RD11a] And [PF7a])</code>

3. ## Re: IIF Statement with multiple criteria (Access 97)

IIF([EL1a]="No",1, IIF([CA1a]="No",1, IIF([RD11a]="No",1, IIF([CD15a]="No",1,IIF([PF7a]="No",1,0 ) ) ) ) )

If I have all my ( , ), and " "'s in the right place, this should work.

4. ## Re: IIF Statement with multiple criteria (Access 97)

Thanks Hans!!!

That worked great.

5. ## Re: IIF Statement with multiple criteria (Access 97)

Thanks Richard,

That took care of the Null values that I found in some of the data.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•