Variable number of parameters in a query?

T

tjtjjtjt

Is it possible to vary the number of parameters that can be used as criteria
for a given field in a query?
Using OR doesn't produce ideal results because users would need to respond
to a parameter box for all possible entries (12), when in fact they usually
only need between 3 and 6.
I tried using nesting a parameter inside of an In expression, but Access
tells me that I've either mistyped the expression, or that it is too complex.
It must be 'too complex', because it works fine if I hard code options
instead of trying to use a parameter box.

I've seen other posts that suggest avoiding variable criteria when possible.
I'm not an Access programmer, and I've been asked this question. Could
someone point me towards an answer, or some resources that will help me
develop one?

Thanks,
 
T

Tom Wickerath

Hi tj,

I know you stated that you're "not an Access programmer", but I think you'll
find that this is the easiest and most user-friendly way of providing this
functionality. The general idea is to have a form with a multi-select list
box that displays the 12 possible choices to the user. The user selects 1 or
more choices and executes the query by clicking on a command button. This
technique is covered in Access books geared towards the intermediate to
advanced user, generally under the topic "Query by Form". This is not the
same QBF that is built in to the Access user interface.

Behind the scenes, you use VBA code to interrogate the list box for the
selected items. You build the WHERE portion of a SQL (Structured Query
Language) statement on-the-fly. This SQL statement becomes the recordsource
for a subform that is included on the main search form.

In order to implement this technique, you will need to learn some SQL and
some VBA. That might sound like a tall order at this point, but it is
certainly do-able. If you send me a private message, with a valid e-mail
address, I will send you a sample database that shows this functionality.


Tom

AOS168 @ comcast . net (<---just eliminate the spaces)

___________________________________

:

Is it possible to vary the number of parameters that can be used as criteria
for a given field in a query?
Using OR doesn't produce ideal results because users would need to respond
to a parameter box for all possible entries (12), when in fact they usually
only need between 3 and 6.
I tried using nesting a parameter inside of an In expression, but Access
tells me that I've either mistyped the expression, or that it is too complex.
It must be 'too complex', because it works fine if I hard code options
instead of trying to use a parameter box.

I've seen other posts that suggest avoiding variable criteria when possible.
I'm not an Access programmer, and I've been asked this question. Could
someone point me towards an answer, or some resources that will help me
develop one?

Thanks,
 
Top