Multiple IIF statements 1

[sepia]

I'm trying to build a report from a query that has some boolean fields. I want to put some text in a unbound text box after evaluating the boolean fields (put text if true).
There are 9 boolean fields which will be evaluated so there can be up to 9 "pieces" of text in the unbound text box but it's unknown which boolean fields are true. I also want to concatenate the "pieces" of text in the unbound text box. Would using multiple IIf statements accomplish this or would it stop at the first true IIf statement?

Ex: IIf([Local School Districts]=-1,"Local School Districts",IIf([Academic Educators]=-1,"Academic Educators","", .......))

Thanks in advance.

 

[CosmoKramer]

Set this up in the unbound text box:

IIf([Local School Districts]=-1,"Local School Districts, ","") & IIf([Academic Educators]=-1,"Academic Educators, ","") & IIf.......))

 

[tempclerk]

You would need to nest your IIfs, more like:
IIf([Local School Districts]=-1,"Local School Districts" & IIf([Academic
Educators]=-1,"Academic Educators","&quot,"&quot

IIf(condition1, truepart & IIf(condition2, truepart & IIf(condition3, truepart, falsepart), falsepart), falsepart)

 

[Sawatzky]

dear sepia ,

as you did it the iif statement would stop after the first true evlauation.
myvariable=iif(condition,truevalue,falsevalue)

is an abbrev for

if condition then
'executed when condition is true
myvariable = truevalue
else
'executed only when condition is false
myvariable = falsevalue
end if

putting another iif into falsevalue leads to this:

if condition then
'executed when condition is true
myvariable = truevalue
else
'executed only when condition is false
myvariable = iif(condition2,truevalue2,falsevalue2)
end if

just for explanation.


Now to solve your problem: (I hope I got it right)

you take the iif-statements and concatenate them like this:

IIf([Local School Districts]=-1,"Local School Districts; ","&quot & IIf([Academic Educators]=-1," Academic Educators;","&quot & iif(,,) & and so on
' attention to the additional spaces and separators I added into the truevalue, otherwise you get a long snake of characters
regards astrid

 

[rry2k]

Hi,

whether it stops depends on and if you use else.
I would use a string statement to build what goes into the textbox

 

[UncleJack]

I think you probably could use multiple IIF statements. However, it appears to me you're perhaps trying to work a query a bit too hard. Have you considered any other approaches, such as writing code on the report to evaluate the boolean fields? The thought that occurs to me would be to write something like this:

Sub ReportOpen()
If Forms!Form1!bolField1 = true then Me!Field = "whatever"
If Forms!Form1!bolField2 = true then Me!Field = Me!Field & "whatever2"
etc, etc.
End Sub

A short routine like this would evaluate the state of the boolean fields appending text to the string only if true. Then the constructed string would be dumped into your unbound text box.

Uncle Jack

 

[tempclerk]

Um, yeah. Concatenating would be simpler than nesting.

 

[MichaelRed]

O.K., but in general you are omitting a delimiter between the selected "bits" so (picking on UncleJack) you would end up with "whateverwhatever2whatever3... whatevern[/n]".

It would look much nicer as: "whatever whatever2 whatever3... whatevern[/n]".

Sub ReportOpen()
[tab]If (Forms!Form1!bolField1 = true) then
[tab][tab]Me!Field = "whatever"
[tab]End If

[tab]If (Forms!Form1!bolField2 = true) then
[tab][tab]If (Len(Me!Field)) > 0) then
[tab][tab][tab]Me!Field = Me!Field & " " & "whatever2"
[tab][tab] Else
[tab][tab][tab]Me!Field = Me!Field & "whatever2"
[tab][tab]End If
[tab]End If

'etc, etc.
End Sub



MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over