Multiple IIF Statements

[Guest]

I have several checkbox fields in a query. I want the checkboxes, "if= -1"
to show in another field on the query, the checkbox fields will not show. I
have created the first IIF statement. Can I use Multiple IIF statements in
one field? If so, how do I string them together?

 

[HanSolo]

Not sure about how to literally have a checkbox appear in the query
results (as you stated, Access defaults to -1 or 0 for checked and
unchecked). But regarding multiple IIF statements, you can nest them
in this format:

IIF(argument,then,(IIF(,argument,then,else statement)))

I think that you can get up to about 7 nested IIF's. You may also want
to try using the "select" "case" features of visual basic.

 

[LeAnne]

Hi Susan,

You might want to read about the SWITCH() function in Help...much easier
than typing out many nested IIF's and keeping track of multiple opening
and closing parentheses. The suggestion upthread to use SELECT CASE in
a VB procedure would also work.

LeAnne

 

[Guest]

I was able to nest the IIF statements, however it still doesn't function
exactly the way I want it to. Any suggestions? Here is the statement.

FieldName:IIF([AAA]=-1, "name", IIf ([BBB]=-1, "name2", IIF([CCC]=-1, "Name3".

It does show if even one of the fields is =1. However, if all apply, I need
all of the "then" statements to appear in this field, seperated by a slash.
I have tried "and" and "or", neither work.

 

[John Spencer]

What you want to do is tricky without writing a VBA function.

FieldName: (IIF([AAA]=-1,"Name",Null) + "/") & (IIF([BBB]=-1,"Name2",Null)
+ "/") & (IIF([CCC]=-1,"Name3",Null) +" /")

To strip the trailing "/" off you need something like the following.

IIF(Right((IIF([AAA]=-1,"Name",Null) + "/") & (IIF([BBB]=-1,"Name2",Null) +
"/") & (IIF([CCC]=-1,"Name3",Null) +" /")
,1)="/",Left((IIF([AAA]=-1,"Name",Null) + "/") &
(IIF([BBB]=-1,"Name2",Null) + "/") & (IIF([CCC]=-1,"Name3",Null) +" /")
,Len((IIF([AAA]=-1,"Name",Null) + "/") & (IIF([BBB]=-1,"Name2",Null) + "/")
& (IIF([CCC]=-1,"Name3",Null) +" /") )-1),Null)

I was able to nest the IIF statements, however it still doesn't function
exactly the way I want it to. Any suggestions? Here is the statement.

FieldName:IIF([AAA]=-1, "name", IIf ([BBB]=-1, "name2", IIF([CCC]=-1,
"Name3".

It does show if even one of the fields is =1. However, if all apply, I
need
all of the "then" statements to appear in this field, seperated by a
slash.
I have tried "and" and "or", neither work.

Not sure about how to literally have a checkbox appear in the query
results (as you stated, Access defaults to -1 or 0 for checked and
unchecked). But regarding multiple IIF statements, you can nest them
in this format:

IIF(argument,then,(IIF(,argument,then,else statement)))

I think that you can get up to about 7 nested IIF's. You may also want
to try using the "select" "case" features of visual basic.