Applying multiple criteria

Question

Split from this thread.

Thank you for a very helpful answer! My question is similar - is there a way to apply multiple criteria to a query, but with the criteria stored in a table? Every row in the criteria table consists of a different set of criteria, and the users may leave one or several blanks. I'd like the query to evaluate the criteria in every row (with AND, treating the blanks as wildcards) and selecting all the data rows from the data table that fulfills all the criteria in one (or more) of the criteria rows in the criteria table. Is this possible? It would mimic making one query for each criteria table record and presenting the results together, but the number of records in the criteria table may vary.

I've searched the forums, but to no avail. Your skillful help would be much appreciated!

Answer 1

Frankly, I think you are taking a complex approach that is unnecessary.

Access offers built in tools that would be easier to use.

Try looking at Split forms. In a split form, Access displays the data in a single record view. AND a datasheet view. The datasheet view in Access includes a feature I call Datasheet Filtering. In datasheet filtering each column in a datasheet has a pull down arrow in the column header. When you click the arrow, it opens a dialog where you can apply filters for that column. The dialog is datatype dependent so it gives many options for filtering. You can add filtering for multiple columns This allows you to drill down to the records you want to see. You can then capture the filter to use in a query.

I suggest you explore this feature before trying something complex.

Answer 2

Thank you all for your prompt replies!

I’m a newbie, so please explain to me in very basic ways.

This is a fictional example: I have a TablePersons, TableCriteria and want to build QuerySelectPersons.

TablePersons contains the following records

Surname, LastName, Age, Mood

Hannah, Andersson, 47, grumpy

Bob, Andersson, 52, happy

Will, Jordan, 34, delighted

Dave, Jordan, 7, thoughtful

Mary, Jordan, 7, delighted

TableCriteria contains these records

Surname, LastName, Age, Mood

,,>40, (this record contains no information about surname, last name or mood. The fields are blank.)

,Jordan, ,delighted (no information about surname or age)

The QuerySelectPersons should, from TablePersons, select Hanna and Bob (because the first record in TableCriteria stipulates that we’re looking for all persons older than 40 years) and Will and Mary (since we’re also looking for all delighted Jordans). The blank fields in TableCriteria are wildcards, ie they do not exclude any records.

My users could add several records to the TableCriteria. They might even add a new record and type “delighted;thoughtful” (two criteria in one field) in the TableCriteria column Mood, hoping to select Will, Dave and Mary in addition to the other selected records.

Answer 3

It would mimic making one query for each criteria table record and presenting the results together, but the number of records in the criteria table may vary.

Hi AnotherA...,

Sure it is possible, but not standard in Access. For that you need VBA, or - what I read in Duane's answer - use his QBF-applet.

A similar approach is standard build-in in any of my applications.

The trick is that you create a dynamical WHERE clause containing only the field where you want to filter on.

As an example:

The column "Veld" shows the fieldnames of the Persoon-record.

The column "Waarde" is to be filled with the values to filter on.

The column "Conditie" shows the default condition for that field, that can be overwritten by pressing a value from the "Selecteer conditie" column.

The values in "Selecteer conditie" are dependant on the fieldtype of the field.

For text-values you can choose from:

beginnend = starting

gelijk = equal

bevat = containing

eindigend = ending

joker = wildcard

AND-woorden = multiple "contains" that must all be present

OR-woorden = multiple "contains" of which at least one must be present

tussen = between

heel\_woord = whole word

sound = more or less the same sound

Null

Not Null

The list is dynamical, so a new condition is easily added.

For Date-values you have the common selections, but you can also filter on the "jaar" = Year.

The underlined fields are FK-fields. Then you can choose from a related table, that returns or one ID, or a set of ID's dependng on further conditions.

Pressing the "Zoek"-button composes the WHERE clause, and displays the qeury

On that Overview-form there is also a "Zoek"-button to modify the used WHERE clause, using the last values.

There is also the option to use pre-defined WHERE clauses in the column "Voorselectie", or use any combination of ID's through the button "Id"

Very soon the above form will be extended with a choice which fields will not be displayed in the Overview-form, and on which fields it will be sorted.

So, many, many possibilities, standard available in my dynamical environment.

It is even possible to show query-results from external databases.

Imb.

Answer 4

......... is there a way to apply multiple criteria to a query, but with the criteria stored in a table?

You could step through the rows in a recordset based on the table in code, and build a string expression for a WHERE clause, with which you could then build the SQL statement for a query and assign that to the RecordSource property of a form or report in the form's or report's Open event procedure.

I'm not clear as to what you mean by 'treating the blanks as wildcards'.  Can you give some real world examples?

Answer 5

The Query By Form in these samples allows a user to create a WHERE clause from records in a table. You choose a field, operator, and enter filter values. Code behind a button loops through the records building a query which is displayed in a datasheet subform.

Let me know if you have questions or the DH doesn’t work for you.