By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,501 Members | 2,871 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,501 IT Pros & Developers. It's quick & easy.

IIF Statements

P: 54

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

Expand|Select|Wrap|Line Numbers
  1. =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!
Feb 19 '10 #1
Share this Question
Share on Google+
4 Replies

P: 122
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:

Expand|Select|Wrap|Line Numbers
  1. =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.
Feb 19 '10 #2

Expert 100+
P: 234
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:

Expand|Select|Wrap|Line Numbers
  1. =IIf(Nz([SubFormValues2])="", [SubFormValues], 
  2.  IIf(Nz([SubFormValues3])="", [SubFormValues] & [SubFormValues2], 
  3.  [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!
Feb 19 '10 #3

Expert Mod 15k+
P: 31,494
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.
Feb 21 '10 #4

P: 54
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 +!
Feb 22 '10 #5

Post your reply

Sign in to post your reply or Sign up for a free account.