Access Query to Accept multiple check box parameters (1 Viewer)

[Euge]

Hi,

I really hope someone will be able to help me with this one as I am sure im just missing something simple.

I have an unbound form which has 20 yes/no unbound check boxes. The purpose of the form is to allow users to tick the various fields and a subform return the results. The subform, which does requery when a check box is ticked is based off a query. Initially, I wanted all the records to display before any check boxes are ticked so I have used the following criteria:

Like IIf([Forms]![Search]![Field1]=False,"*",[Forms]![Search]![Field1])

Which basically reads if field1 is no then display all records, else display all yes. Now that works fine but what I would like to have working is that if a client ticks field1, field2, and field3 it displays all records that have ‘yes’ in either field. Currently, if more than field is ticked the query treats it like:

Field1 And Field2 And Field3 And etc = true

I want to be able to select several check boxes and have the query return results for each check box that was checked. I would like to avoid doing this by having an append and delete query per checkbox.

Thank you

 

[KenHigg]

So you have 20 yes/no fields in your table. Then the checkbox filter form has a corresponding checkbox control for each of these fields. So why can't you simply use each of the form checkbox controls as a criteria in the query that returns the results?

???
ken

 

[Euge]

So you have 20 yes/no fields in your table. Then the checkbox filter form has a corresponding checkbox control for each of these fields. So why can't you simply use each of the form checkbox controls as a criteria in the query that returns the results?

???
ken

The problem i have found is that lets say there are 3 clients and 3 yes/no fields:

Client Field1 Field2 Field3
1 Y Y N
2 N Y Y
3 Y N N

Using the criteria... Like IIf([Forms]![Search]![Field1]=False,"*",[Forms]![Search]![Field1]) ... It will show all the clients until something is selected, which is what i want.

If Field1 is ticked it will display clients 1 and 3
If Field 2 is ticked it will display clients 1 and 2

However, what i would like is if field1 and field2 is ticked then it will show all clients that are true in either case so it would select client1 client2 client3.

I hope that makes sense

 

[KenHigg]

Maybe this attachment will help explain where I was going.

ken

 

[Euge]

Hi Ken,

Thank you for your sample database. I have just gone through it and it is similar to what i have now. However, as far as i can see it still has the same limitation as mine...

In your sample, if i only tick field1 then it returns only one record (id11) as fields2,3,4 etc are false.

If i tick field1 and field2 on your sample it returns no results when instead i am aiming to show all yes results for either field1 or field2.

 

[KenHigg]

In the query simply put the criteria on seperate rows and you will get 'or' instead of 'and'...

Make sense?

ken

 

[Euge]

I did try that and i've just tried it again however when it runs it just displays all clients.

For example, if i tick field1, field2, and field3 i would hope it would filter out all clients accept those that have a tick in either one of those fields. But it just doesnt filter when i have OR instead of AND as it selects all clients regardless if they have a tick in field1,2, and 3 etc.

The reason i start the query showing all records is because i want the users to see all the clients before any criteria is selected and then when they start to tick the fields the list adjusts accordingly.

The criteria i am using in the query per fields is:

Like IIf([Forms]![Search]![Field1]=False,"*",[Forms]![Search]![Field1])

Would that have something to do with the query not filtering properly.

Thank you for your patience as im not particularly skilled at this.

 

[KenHigg]

Try something like this on seperate lines in the query to create or conditions:

IIf([Forms]![Form1]![Check1],True)

Change the Check1 to Check2 etc for each column...

ken

edit - see attachment

 

[Euge]

Thank you

Thank you Ken your solution was spot on.

I have been a long time user of this site but this was my first post/thread and i have to say that the level of support on here is unparalleled anywhere else.

So thanks again,

Eugene

 

[KenHigg]

Glad to help - I have to admit you kind of had me stumped -

ken

 

[Dopeyjh]

What do you do if you have say more then 20 check boxes.....say 50? The Query builder does not allow that many fields

 

[KenHigg]

I think I'd opt to build a custom sql query string on the fly in that scenario.

 

[Euge]

Hey, i also had a situation with 30+ check boxes and found that the query builder couldn't handle it. It will however accept 30+ by manually typing in the SQL in SQL view. If you need help to get started make the first 20 in query builder then switch to SQL view and you can just copy whats there and just add your extra fields.

Worked for me

 

[Newbie100]

IIf([Forms]![Form1]![Check1],True)

If you do not need the false-part in the IIF function (i.e. the third argument), you can simply put True in the criteria for the field to return the ticked check boxes.