report needs to show 3 options in a report (2000)

[wsryan]

I have a table with three fields--call them [choice1], [choice2] and [choice3]. I want a report that will show the data from whichever of those three fields is filled (just one text box showing the data from whichever of the three choices was selected). I don't know if I need an "If, then, else, else" or how to do it!

[wsryan]

Is there any advantage to setting that up in the query somehow, or just as good to do it in the report?

[HansV]

You can use the Nz function in the Control Source property of the text box:
<code>
=Nz([Choice1],Nz([Choice2],[Choice3]))
</code>
The Nz function returns its first argument, unless that is null (blank), then it returns its second argument. Here, two Nz functions are nested. It is also possible to use the IIf function, but that's messier:
<code>
=IIf(IsNull([Choice1]),IIf(IsNull([Choice2]),[Choice3],[Choice2]),[Choice1])
</code>
If Choice1 etc. are all text fields, and if you are sure that just one of them is populated for any given record, you can simply concatenate them:
<code>
=[Choice1] & [Choice2] & [Choice3]</code>

[HansV]

A query is more efficient, in theory at least, but the difference is probably only measurable if you are dealing with hundreds of thousands of records.

If you need the result (choice 1 or 2 or 3 in one field) in other places in the database, a query is definitely better.

[wsryan]

OOPS! The "Nz" worked, except I realized some may not choose any one of the three choices--how can I specify to leave blank if none of the three is chosen?

[HansV]

If all of the choices are left blank, the expressions I suggested will all return a blank too, so that should be OK.

[patt]

Hans
You suggested:
=[Choice1] & [Choice2] & [Choice3]
Don't you mean:
=[Choice1] + [Choice2] + [Choice3]

I am assuming that they are text fields of course.

[HansV]

According to wsryan, only one of the fields will be populated, the others will be null. Using + will result in null if at least of the arguments is null, so in this situation, =[Choice1]+[Choice2]+[Choice3] will ALWAYS be null. Using & will ignore nulls, and concatenate the non-null arguments, in this situation the one field that is populated.

[patt]

Hans, you are quite right.

I understood it to be the other way around. I always use & for concatenation.
When would you use + for concatenation? Ignore this question

[charlotte]

Actually, you use + for concatenation when you WANT to return a null if a value is null. For example:

[LastName] & ( ", " + [FirstName] )

This will return <code><lastname>, <firstname></code> if both fields are populated. If only the last name is populated, it will return <code><lastname></code> rather than <code><lastname>, </code>

[wsryan]

That expression (now in the query) works perfectly--now I'm stumped by a similar problem: I want to be able to return a line of text if a field is True: Like,

If [Field1]=True, then "Text1", if not true, look at [Field2]
If [Field2]=True, then "Text2", if not true, look to [Field3]
If [Field3]=True, then "Text3", if not true, then leave blank

How do I do that? Thanks in advance for your help...

[HansV]

You can use nested IIf functions:
<code>
=IIf([Field1], "Text1", IIf([Field2], "Text2", IIf([Field3], "Text3", "")))
</code>
Note: Access will remove the spaces between the parts of the expression, I put them in for clarity.

[wsryan]

Can I say:
=IIf(IsNotNull([Field1]), "Text1", IIf(IsNotNull([Field2]), "Text2", IIf(IsNotNull([Field3]), "Text3", "")))

Or what is the right syntax? Thanks for your patience with me! How's the wather in the Netherlands today?

[HansV]

IsNotNull doesn't exist, instead use Not IsNull(...), or IsNull(...) = False:

<code>=IIf(Not IsNull([Field1]), "Text1", IIf(Not IsNull([Field2]), "Text2", IIf(INot sNull([Field3]), "Text3", "")))</code>

<img src=/w3timages/blueline.gif width=33% height=2>

We've been having very unusual weather here. Usually, winter is more or less over by the second half of February, but it's been quite cold for almost two weeks now, with the heaviest snowfall in 50 years on the 2nd of March, and record low temperatures for March last night (-20.7 C = -5 F). More snow to come this afternoon. I know it's nothing special for many regions in the US, but it is for us. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

[wsryan]

My next problem is on a form for people to order a quantity of DVDs for $10.00 each, I have a field [DVDOrd] which I have set as a number for field type. Another field called [DVDFee]. How do I get the right fee to show up in that field (how do I calculate the number in [DVDOrd] X $10.00)?

Thanks for your help!
Warren