Nested IIF statement to produce 1 of 3 results

Question

I am trying to piece together a nested "IIF" statement to produce one of three Values but my line is not working

=IIF(Fields!LeaseNumber.value = "", "Vacant",Fields!tenantName.value) AND (IIF(Fields!tenantName.Value ="",Fields!LeaseDBA.Value, Fields!tenantName.Value))

My expected outcome should be:

If No lease# - "Vacant"

if no tenantName - "LeaseDBA"

all other rows just give me - "tenantname"

All the other questions I reviewed didn't seem to match this type of IIF clause. Any help really is appreciated.

Answer 1

I've actually recently just tried to expalain how the IIF expression works and how to nest them. https://stackoverflow.com/a/35289515/4579864

To check if a value is null or empty, you're better off using one of these functions:

• You can use IsNothing() to check if there is a value. Applied to your example would result to this:

  =IIF(IsNothing(Fields!LeaseNumber.value)
  ,"Vacant" 
  ,IIF(IsNothing(Fields!tenantName.Value), Fields!LeaseDBA.Value, Fields!tenantName.Value))
  

• You can calculate the length of your value with Len() and then check if it is larger than 0.

  =IIF(Len(Fields!LeaseNumber.value) > 0 
  ,IIF(Len(Fields!tenantName.Value) > 0, Fields!tenantName.Value, Fields!LeaseDBA.Value)
  ,"Vacant" )