result -1 in Iff statement

[newtoAccess]

I have a IFF statement that checks a check box is on/off. The result is always a "-1" How do I get it to be something else?



IIf([Provincial Guest]=yes,1) Or IIf([District Guest]=Yes,2) Or IIf([Officers "G"]=Yes,3) Or IIf([Chairmen "G"]=Yes,4) Or IIf([Council "G"]=Yes,5) Or IIf([Zone Commader]=Yes,6)

all the results are "-1"

I also tried
Guest2: IIf([Provincial Guest]=Yes,"A"); IIf([District Guest]=Yes,"B") and got a "-1"

Guest2: IIf([Provincial Guest]=Yes,"A")or IIf([District Guest]=Yes,"B") and got a "-1"

What am I doing wrong?

[RuralGuy]

I take it only one of them will be "yes" at a time? You are trying to do a nested IIF statement, right?

[newtoAccess]

Yes, only one will be yes,
Nested iif ?? maybe.
Trying to get the results into a single field.

[RuralGuy]

Try:
IIf([Provincial Guest]=True,1, IIf([District Guest]=True,2, IIf([Officers "G"]=True,3, IIf([Chairmen "G"]=True,4, IIf([Council "G"]=True,5, IIf([Zone Commader]=True,6,0))))))

[newtoAccess]

How big can I make this IFF?

At present I have 13

[RuralGuy]

You can nest IIF's up to 7 levels.

[RuralGuy]

Since these are mutually exclusive settings, have you looked at the OptionControl?

[ssanfu]

For more that 7 tests, you could use the Switch() function. It would look like :

Switch([Provincial Guest]=True,1, [District Guest]=True,2, [Officers "G"]=True,3, [Chairmen "G"]=True,4, [Council "G"]=True,5, [Zone Commader]=True,6)

I would put these in a lookup table:

GuestID| Description
--------------------------
1|Provincial Guest
2|District Guest
3|Officers "G"
4|Chairmen "G"
5|Council "G"
6|Zone Commader


Then you can use a combo box, or list box to male a selection. Also, it is easier to add a new selection.

BTW, you should only use letters, numbers and the underscore in field names........

[newtoAccess]

WOW!! I will have to play with Switch function and look up tables.
I have never used them or know how to and where to use them.

I will give it a try this weekend.

Thanks

[ssanfu]

One more thing..... You should only use letters, numbers and underscores in your object names. No one should see the field names, so quotes (as in Officers "G") or spaces really doesn't matter.

Plus, some special characters are also delimiters (" ' # ). Using them in names will cause you headaches later on.

Good luck

[newtoAccess]

Thanks, I have not had time to play<Learn> with switch().

thanks