Multiple IIF Statements

  • Thread starter 2Blessed4Stress
  • Start date
2

2Blessed4Stress

I have a table that contains Company 1,2,...5 in seperate columns. In my
report I want to show the following:
If Company 5 is "", company4, if company 4 is "" company 3....etc.....
How do I do this?
 
O

Ofer Cohen

Try something like

=IIf([Company5] <> "" And [Company5] Is Not Null,[Company5], IIf([Company4]
<> "" And [Company4] Is Not Null,[Company4], IIf([Company3] <> "" And
[Company3] Is Not Null,[Company3],IIf([Company2] <> "" And [Company2] Is Not
Null,[Company2],[Company1]))))
 
D

Duane Hookom

I would seriously look at normalizing your table structure.
However, if the fields are actually Null and not a zero-length-string (""),
you can use:
=Nz([Company 5], Nz([Company 4], Nz([Company 3], Nz([Company 2], [Company
1]) ) ) )
 
2

2Blessed4Stress

Thanks! It works great.

Ofer Cohen said:
Try something like

=IIf([Company5] <> "" And [Company5] Is Not Null,[Company5], IIf([Company4]
<> "" And [Company4] Is Not Null,[Company4], IIf([Company3] <> "" And
[Company3] Is Not Null,[Company3],IIf([Company2] <> "" And [Company2] Is Not
Null,[Company2],[Company1]))))

--
Good Luck
BS"D


2Blessed4Stress said:
I have a table that contains Company 1,2,...5 in seperate columns. In my
report I want to show the following:
If Company 5 is "", company4, if company 4 is "" company 3....etc.....
How do I do this?
Click to expand...
Click to expand...
 
Top