IIf Query Criteria - How to Select True, False or Both?

[DBLoser]

I have a query with a True/False field and a form with an option group with options 1=Both, 2=True, 3=False.

I tried using a nested IIf but am not sure how to code the Both option to select all records. In other words, if option 1 is clicked then select ALL records. If option 2 is clicked select TRUE records. If option 3 is clicked select FALSE records. It is the ALL that I am struggling with (like a wildcard).

Here is the criteria:

IIf([Forms]![formMyForm]![frameComplete]=3,False,IIf([Forms]![formMyForm]![frameComplete]=2,True,Like"*"))

Notice the Like"*". This is my attempt to select all records but it doesn't work. It only selects TRUE records whether option 1 or 2 is clicked (Both/True).

 

[DBLoser]

Solved!

IIf([Forms]![formMyForm]![frameComplete]=3,False,IIf([Forms]![formMyForm]![frameComplete]=2,True,[myTable]![Complete]))

Which means, use whatever value is assigned to the Complete field or another way of writing SELECT ALL.

 

[PWise]

I would do this simpler

I would give the options buttons the values

-1=True
0=False
1=all

and the iff()

IIf([Forms]![formMyForm]![frameComplete]=1,[myTable]![Complete],[Forms]![formMyForm]![frameComplete])

and no need for nested iif

 

[DBLoser]

So does a Yes/No field recognize the value of 1? Or would I change the field type to a list with those three options?

 

[PWise]

Actually any number excpet for 0 translates as true, but
Access stores a true as -1 and a false as 0

and the iif translates as

if [Forms]![formMyForm]![frameComplete]=1 then
return me all records that that complete equals complete
else
return me all records that complete equals the value of [Forms]![formMyForm]![frameComplete]=
end if

all that you have to change is the values of the options buttons and simplfy the iif statement