IIF Statements

[g diddy]

Hi

I'm using the following IIf statement to populate a field in a report.

=IIf([SubFormValues2]="",[SubFormValues],IIf([SubFormValues3]="",[SubFormValues]+""+[SubFormValues2],[SubFormValues]+""+[SubFormValues2]+""+[SubFormValues3]))
 

SubFormValues1 (2 and 3) are all from the same query - (I have had to do it like this in the report as in the query the maximum length of 255 characters had been reached in all 3 fields). The problem is when I run the report I get blank fields in every page where SubFormValues2 or SubFormValues 3 has been blank - which basically means that the only time the text appears is when there is information in all 3 fields for the same record i.e. SubFormValues1, 2 and 3. I'm quite stumped by it as the logic seems correct - if subform values 2 is blank just show the first field, if subformvalues 3 is blank just show the first concatenated with the second. else show all 3. Can anyone see what i'm doing wrong?

Thanks for your time!

[gershwyn]

If SubFormValues2 and SubFormValues3 contain no data at all, then they are probably Null and not a blank string. If that is the case, then the first two tests will fail (since null is not equal to a blank string) and will get a null value for the final answer. (Any expression that contains a null will evaluate to null, whether or not the other fields contain any data.)

Try this and see if it works:

=IIf(IsNull([SubFormValues2]),[SubFormValues],IIf(IsNull([SubFormValues3]),[SubFormValues]+" "+[SubFormValues2],[SubFormValues]+" "+[SubFormValues2]+" "+[SubFormValues3]))

That would take care of testing for nulls, assuming you never have a situation where SubFormValues3 has data but SubFormValues2 does not.

I don't know where this data is coming from or how you intend to use it. There is probably a way to what you intend without nested IFs.

[topher23]

For a more elegant solution, use Nz rather than IsNull (just in case the value actually is an empty string). You should also be able to concatenate the strings by using & rather than +""+

The expression would look something like this:

=IIf(Nz([SubFormValues2])="", [SubFormValues], 
 IIf(Nz([SubFormValues3])="", [SubFormValues] & [SubFormValues2], 
 [SubFormValues] & [SubFormValues2] & [SubFormValues3])) 
 

If you find yourself doing a lot of nested IIf's, you may also want to look into the Switch function.

Hope this helps!

[NeoPa]

I would certainly us & instead of + when concatenating these values as with the + any Null values would be propagated (resulting in the whole string being Null). See Using "&" and "+" in WHERE Clause for more on this.

[g diddy]

Thank you very much for the snippet and explanation topher23; I have got it working perfectly now! Also thank you NeoPa for that guide, it was very useful. I didn't realise that there was a difference between & and +!