Queries - Use multiple queries

I have a search menu where the user can choose a search option:

1. Search by Date Filed
2. Search by Paid Date
3. Search by Invoice No.
4. Search by Client Name

etc....

I have a query built for each of these options, 14 in all. Is there a way that I could have the user enter more than one option, and link those queries together? For example, they could enter 1 and 4 to search for Client Name on a particular filing date.

I would rather not have to write a query for each and every option...

Thanks.

You would need to write VBA code to check every checkbox to see if it has been selected or not. Your code would then build the SQL syntax to do that. It's done all the time, but it can be quite a job to write it--and debug it, with that many options.

Thank you...I'll see if I can figure out the code.

If you're looking all this information up from one table you can do it with one query. Let's say you have 5 fields in your table and you want to be able to look up by any one of those 5 fields and you have a form that the user can put in their search criteria.

so Tbl_Test (void is a yes/no, purchdate is a date)


Now depending on the nature of your ID fields you could have one query that was (I'm assuming Cust_ID and Item_ID are numbers for the purposes of this example):

now assuming your form that you set your criteria in is called Frm_Criteria and the fields are labeled Fld_001 through Fld_005 corresponding to the five fields:



Something like this query will show pick up the value from your input form if it exists, if it doesn't it will pick everything. The only place you're run into trouble is if your ID is an autonumber then you would have to do something fancier to get that to come out right. If, however, you're using independently generated ID's (which I usually do) it should work fine.