Can a query be written to search on a variable number of criteria.

[Guest]

I am using Access 2000 to write a Recipe database. I want to have a form with
check boxes that allows me to search by any one or more of up to 8 categories
(recipe type, special diets, cooking method, recipe name, etc.) - with combo
boxes to select the criteria for each one. I can write queries for any
combination of these fields but that requires a huge number of queries. Is
there any way to get Access to run a query based on any combination of fields
so that this input could be combined into a single, highly adaptable, query?

 

[John Vinson]

I am using Access 2000 to write a Recipe database. I want to have a form with
check boxes that allows me to search by any one or more of up to 8 categories
(recipe type, special diets, cooking method, recipe name, etc.) - with combo
boxes to select the criteria for each one. I can write queries for any
combination of these fields but that requires a huge number of queries. Is
there any way to get Access to run a query based on any combination of fields
so that this input could be combined into a single, highly adaptable, query?

The simplest way is to write VBA code which will loop through the
controls on the Form and build up a SQL string, criterion by
criterion. THe checkboxes aren't necessary - simply check to see if
the textbox or combo box is NULL, and if it's not, add a criterion.
Frex:

Dim strSQL As String
strSQL = "SELECT thisfield, thatfield, theotherfield" _
& " FROM Recipes WHERE TRUE"
If Not IsNull(Me!cboType) Then
strSQL = strSQL & " AND [TypeID] = " & Me!cboType
End If
If Not IsNull(Me!cboSpecialDiet) Then
strSQL = strSQL & " AND [SpecialDietID] = " & Me!cboSpecialDiet
End If
<etc>

The TRUE is so that you get a valid WHERE clause rather than one with
the WHERE clause starting with the word AND.

You'ld then set the Recordsource of a Form (for onscreen) or Report
(for printing) to the resulting SQL string.

John W. Vinson[MVP]

 

[Guest]

Hi John,
Thanks for your answer to my posted query. It certainly helped. Is that the
way that commercial programs that allow an “any†selection in a combo box
work? Or is there another way?

I am using Access 2000 to write a Recipe database. I want to have a form with
check boxes that allows me to search by any one or more of up to 8 categories
(recipe type, special diets, cooking method, recipe name, etc.) - with combo
boxes to select the criteria for each one. I can write queries for any
combination of these fields but that requires a huge number of queries. Is
there any way to get Access to run a query based on any combination of fields
so that this input could be combined into a single, highly adaptable, query?

The simplest way is to write VBA code which will loop through the
controls on the Form and build up a SQL string, criterion by
criterion. THe checkboxes aren't necessary - simply check to see if
the textbox or combo box is NULL, and if it's not, add a criterion.
Frex:

Dim strSQL As String
strSQL = "SELECT thisfield, thatfield, theotherfield" _
& " FROM Recipes WHERE TRUE"
If Not IsNull(Me!cboType) Then
strSQL = strSQL & " AND [TypeID] = " & Me!cboType
End If
If Not IsNull(Me!cboSpecialDiet) Then
strSQL = strSQL & " AND [SpecialDietID] = " & Me!cboSpecialDiet
End If
<etc>

The TRUE is so that you get a valid WHERE clause rather than one with
the WHERE clause starting with the word AND.

You'ld then set the Recordsource of a Form (for onscreen) or Report
(for printing) to the resulting SQL string.

John W. Vinson[MVP]

 

[John Vinson]

Hi John,
Thanks for your answer to my posted query. It certainly helped. Is that the
way that commercial programs that allow an “any” selection in a combo box
work? Or is there another way?

The suggestion I made will return all records if the criterion is left
blank; but you can also add a line to the combo's row source with
"ANY" as its visible expression, and use that in the SQL. Simply don't
apply a criterion to that field if the user chooses the ANY option.

John W. Vinson[MVP]