Multiple Queries

Jonathon,

Please find an extract from my Weekly tips Newsletter.

It should point you in the right direction. If you still need clarification, please do not hesitate to contact me.

If I am maxed out you can email me at:

AllExpertsQuestion@simply-access.com

**********************************

*******************************************
Multiple field selections in a form to filter your data.
*******************************************
This week's topic, is thanks to one of my subscribers.
Some weeks ago, you may remember a rather lame attempt
of trying to explain how best to set up your query and form
to filter information.

As an example you may have 5 fields on a form, and you
want the user to be able to enter data into one or more
of these fields, the underlying query would then be
filtered by the information added to these fields.
Well the method I suggested was quite tedious and
time consuming and I am always on a look out for
a better way of doing things, and thankfully I was
provided with one.

I have included below the scenario I used in my previous example.

*****************************************
This week we will look at setting up a form that has
more than one criteria (parameter) for a query,
but having the option of only choosing one of the criteria.
i.e. if you had the following fields you wish to be able
set criteria ‘Author', ‘Publishing Company' and
‘Type of book' (novel, reference etc).
You may wish to query only those written by a certain
author, but not by anything else, or you may wish
to query all Novels by a certain Publishing Company,
but to include all authors.

You can set this up by utilizing an unbound form to
set the criteria for the underlying query.

To do this create a new unbound form; add an unbound
text box for each of the criteria you wish to set. In the
above example you would add three. One for the author,
one for the publishing company and one for the type of
book.

Then build the query, which contains the fields, you wish to
set the parameters for and any other relevant fields.

Now using the above example in the Author column in
the criteria row type the following:

Like "*" & [forms]![queryform]![querytextbox1] & "*"

In the same rows, type the same thing in the columns
for the publishing company and the type of book.

***Jonathon - your scenario is a bit different. You would add this expression one under the other in the criteria section under the sng title. You will need to change the name of queryform to the name of your form, and querytextbox1 to the name of your first combo box, then for the second instance to the name of your second combo box etc

For those of you who read my previous articles on this,
I know you would agree this is a better solution.

So if any one else has any good ideas they would
like to share, just let me know.
***************************************

Hope this helps.

Regards

Julie