Multiple IIF Statements

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Susan - 12 Jan 2006 22:03 GMT

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?

Reply to this Message

HanSolo - 12 Jan 2006 22:13 GMT

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.

Reply to this Message

Susan - 13 Jan 2006 17:24 GMT

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
[quoted text clipped - 5 lines]
> 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.

Reply to this Message

John Spencer - 13 Jan 2006 19:38 GMT

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.
[quoted text clipped - 17 lines]
>> 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.

Reply to this Message

LeAnne - 13 Jan 2006 14:02 GMT

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

> 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?

Reply to this Message