Multiple IIF Statements

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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]))))
 
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]) ) ) )
 
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...
 
Back
Top