Search Yes/No with No=Wildcard. How?

[leicklda]

I need to filter records based on whether they have "yes" values for various fields - but NOT on whether they have "no" values for other
fields.

Table: "Lots"
[A] [C] [D] [E]
yes no yes no no
no yes no no yes
no no no yes no

Query: "qryLots"
Base on table "Lots" - sets criteria
[A] Where "Like [Forms]![Search]![A]"
Where "Like [Forms]![Search]!" etc...

Form "Search"
Top: Multiple unbound checkboxs [A],etc.. 1 for each yes/no in "Lots"
Bottom: Listbox "lots" which lists the filtered records "qryLots".
Button: refreshes listbox "lots" after search criteria has been checked.

When I want to search for records where [A]=yes, I check the "A" control on the form. This works, but it will only return records which have a yes value in field A and ONLY in field A. If [A] and [F] both have yes values, the record will not show because the control for
[F] is set to no. But I don't care what F is equal to, only A. I still do need the F checkbox there though in case I need to do a searth on records with BOTH A and F selected. So I need to do the following:

If the search value of [A] is "yes" - always return records where [A]=1 and filter out any records where [A]=0
If the search value of [A] is "no" - let it act like a wildcard - [A] either CAN or CANNOT =0.

Any ideas on how to accomplish this? I tried iif statements, but they got really ugly. Can I set 0=* somehow instead of 0=0 while still using checkbox controls?

Or am I going about this totally wrong?

 

[PWise]

i would bind the query directly to the form bur rether change the sql for the query
dim mydb as database
lots=iif(me.chklots=true,true,"[lots])
sun=iif(me.chksun=true,true,"[sun])
morning=iif(me.chkmorning=true,true,")
set mydb=currentdb
mydb.querydefs("queryname).sql="select....from tablename where lots=" & lots & "and sun=" & sun & "and morning=" & morning & ..... & ";"

 

[PWise]

So that’s where my cup of coffee went I did not put in there
It should read open bracket "["morning"]" close bracket
Also it should start I would "not" bind... On second thought you can bind to the form and in each criteria put my iif statement