Multiple selection on combo box for parameter query

[nobuddy]

I am creating a database that needs to have maximum user-friendly capabilities to query. The users query the database to come up with a sample to market new food products to. Participants in the sample are broken down into segment (e.g., colleges, schools, fast food), states, volume levels, etc. The problem is, when they run a query to determine their sample, they might be looking for the following:

(1)All Colleges in the states of IL, IN, or OH, where the volume level in each of the states is at least $500K. And, (2) all schools in the states of FL, CA, and GA. The parameters among the segments (12 possible) will all be different. (Yuk!)

I created a form that opens in filter by form and the user makes selections using as many OR tabs as necessary. Then they choose Apply Filter so that they can activate the command buttons on the form to "Save As Query," etc. I've shown the user what this looks like, but the filter by form has them thrown for a loop. They don't like to have to click on the FilterByForm and ApplyFilter and LoadQuery buttons in the toolbar.

I thought about doing this originally with a regular form and using combo boxes with multiple selection available. But I tried this and couldn't get the parameter query to run. If I selected a single item from the combo box, it worked perfectly. But if you selected more than one item, it returns no records. Do I need a different reference in the criteria on the parameter query when the parameters are coming from a multiple selection combo box?

Does anyone have any better suggestions for how to do this? Basically, they could have any combination of AND's and OR's on the criteria for the query. I don't know how to make it user-friendly.

Any help would be appreciated...but I don't know Visual Basic! [sig][/sig]

 

[Jonathan]

Hate to say it, but I reckon the best way by far to do this is to use VBA. Sorry. I would probably set up a form to allow them to specify the parameters, as you have already done, then write some VBA code behind the form to build up a SQL string which can then be used to get your data.

If you are using access a lot, then you should try any learn VBA anyway - you will end up with much more user friendly and robust apps.

A fairly good place to start would probably be

http://msdn.microsoft.com/library/default.asp

Then use the thing on the left to navigate to:
Office Developer Documentation - Office 97 Documentation - Access - Building Applications with Microsoft Access.
[sig]<p> Jonathan<br><a href=mailto:j.w.george@virginnet.co.uk>j.w.george@virginnet.co.uk</a><br><a href= > </a><br>Working against: Visual Basic 6, Access 97, Visual Interdev 6, VBScript, Active Server Pages, SQL Server 6.5, Oracle 7[/sig]