Converting Excel IF to Access iiF???

[robertr]

Hi - I am stuck on how to create an iif statement in Access that will look at mulitple rows and return the expected vaslues in one column. Here is an example. I appreciate any suggestions. Thanks!



V2 = Source column
DE2 = Source Column

IF(AND(V2="EMP+CHILDREN",DE2="N"),"Tobacco Free Associate + Child(ren)", IF(AND(V2="FAMILY",DE2="Y"),"Associate + Family","Tobacco Free Associate + Family"))

[alansidman]

Assuming that V2 and DE2 are your field names in Access, then this is the formula in a new field:

=iif([V2]="Emp+children" and [DE2]="N","Tobacco Free Associate + Child(ren)",iif([V2]="Family" and [DE2]="Y","Associate + Family","Tobacco Free Associate + Family"))

Alan

[robertr]

Thanks so much!!!

What about if I want to add to the end a default value/column if the criteria is not satisfied?

[alansidman]

I am not understanding. Your iif statement false section determines that. What am I missing? Perhaps and example of what you are attempting.

[robertr]

THIS WORKED GREAT!!

My question is how do I defer to a desired default column value if the iif statement doe nor return as expected? I added the [PLAN] to the end of the statement but am receiving and error.

Plans2: IIf([PLAN]="EMP" And [SMOKING]="N","Tobacco Free Associate Only",IIf([PLAN]="EMP+SPOUSE" And [SMOKING]="Y","Associate + Spouse",IIf([PLAN]="EMP+SPOUSE" And [SMOKING]="N","Tobacco Free Associate + Spouse)",IIf([PLAN]="EMP+CHILDREN" And [SMOKING]="Y","Associate + Child(ren)",IIf([PLAN]="Emp+children" And [SMOKING]="N","Tobacco Free Associate + Child(ren)",IIf([PLAN]="Family" And [SMOKING]="Y","Associate + Family","Tobacco Free Associate + Family",[PLAN],)))))

[alansidman]

You currently have a false result for the last item. You need to replace it with another iif statement with a true result and then make the [PLAN] the false result for it.

The syntax for an iif statement is =iif(Criteria, Result if True, Result if False)

You have tried to add a second False statement that Access does not understand.

Alan