Multiple choise filtering

[Chrisopia]

I have 2 tables which I use to filter the main database for various reasons.

1 has a list of around 20 different catagories of business (i.e. cleaners, builders etc...)
the 2nd list is a list of sources, i.e. the person who we got the information from by persons initials (e.g. F, N... etc)

At the moment I am filtering manually through a query design.

using criteria ( Criteria / CatagoryID: = "2"AND"3"AND"4" or something)

this is very easy for me to do but it takes a long time, plus others using the system don't know how to do it. So I want to make a form, full of check boxes, 2 main groups; one for catagory (so there will be 20 tick boxes) and another for source.

i've tried searching on here already but you seem to all talk about SQL.

I had a rough idea of perhaps running a query with aload of IFF statments instead of numbers
(like IIF the box was ticked then add it to the query criteria, if not then dont...

IIF(TickBox01 = True, "1","") AND IIF(TickBox02 = True, "2","")

my logic thinks that for example if 01 is ticked and 02 isnt, the end result should just be 1 being filtered out.
Am I thinking correctly here?

 

[Mike375]

Here is what Ihave a for a similar situation and this is entered for the criteria in the query.

The criterial for one field is

>=[Forms]![Attempts]![1] And <=[Forms]![Attempts]![2] In this case [1] and [2] are dates

In another field it is

[Forms]![Attempts]![C1] Or [Forms]![Attempts]![C2] Or [Forms]![Attempts]![C3] Or [Forms]![Attempts]![C4] and the full thing goes to [Forms]![Attempts]![C32] In this case the text box is for a number entry that is for the category of prospect for telemarketing.

It does not require all 32 to have an entry, one is enough and it does not matter which box the entry is made. So if a 32 is put in the first box and a 10 in the next box it gets the prospects who are category 10 and 32.

Because both sets are on the same criteria line it is an AND between them, that is, the category of prospects are selected but only within the date range

I have a little tabular form that opens and clicking inserts the selected categories.

 

[Chrisopia]

Hey, could you please attach a sample of this? - I can't seem to get the hang of it. Your methid and my method didn't seem to work.

My method kept returning a "the expression is to complicated" error, and the switch function didnt do too well either.