multiple criteria

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Griff - 13 Jul 2006 17:29 GMT

Hello;

I am designing a Query By Form as suggested in KB 95931
There are 11 different fields on the form which will be used in the query
criteria.

When I set up the Criteria, the instructions say to make sure that the
criteria for each field is listed in a single criteria line, and that if the
statements are on two separate lines there will be unexpected results.

So I enter the criteria on a single line, but then for some reason the form
works well for only one of the criteria and not any of the others. When I go
back to the query, Access has taken the criteria and put them on separate
lines, and has even set up new columns for the "Is Null" criteria.

What's going wrong here?

Thanks,

Griff

Reply to this Message

Allen Browne - 13 Jul 2006 18:49 GMT

That technique gets messy really quickly when you have lots of criteria, and
can be difficult to prevent the query design view from messing up the
combinations of AND and OR.

If you don't mind a little VBA code, this article explains an alternative
approach:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html
The idea is to build a string to filter the search form, so the filter
string is only searching on the boxes where the user actually entered
something.

It includes a database to download and see how it works.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> I am designing a Query By Form as suggested in KB 95931
> There are 11 different fields on the form which will be used in the query
[quoted text clipped - 11 lines]
> back to the query, Access has taken the criteria and put them on separate
> lines, and has even set up new columns for the "Is Null" criteria.

Reply to this Message