IIF Statement with multiple criteria (Access 97)

Topic

New Reply

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.

Reply | Quote

Replies

You could use

-([EL1a]="No" Or [CA1a]="No" Or [CD15a]="No" Or [RD11a]="No" Or [PF7a]="No")

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

1-([EL1a] And [CA1a] And [CD15a] And [RD11a] And [PF7a])

Reply | Quote

Thanks Hans!!!

That worked great.

Reply | Quote

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.

Reply | Quote

Thanks Richard,

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

Reply | Quote