Querying combining Multiple Fields in Access97

[Jduke]

I am using a form as a search engine. I would like to allow the user to select criteria from a series of combo boxes and have the query search be on that combined multiple criteria.

Thanks in advance!!!

 

[kathryn]

This is not difficult. Set up your form with boxes from which users can select criteria. The form should have a button to run the query.

In the code behind the button, you will build the query on the fly and run it.

What do you want done with the query results? Kathryn

 

[LarryDeLaruelle]

JDuke:

What you want to do is reasonably simple.

In the query, reference the form and control in the criteria cell for the field to be filtered.

Forms!frmSearchform.cboSelection

Use the usual equals, not equals, greater, etc before the reference to the form.

However, if it is possible that the user will not make a selection from one of you combo or list boxes, you will need to build the a query string using SQL code. An alternative is to set a default value for the selection boxes and, in the query, reference it with the Like operator.

That is a bit too complicated to go into here without knowing the exact criteria you will be using. Post more details and you are sure to get more specific help.


Larry De Laruelle
larry1de@yahoo.com

 

[Jduke]

To add to my Combination querying issue I would like to have the ability to have the user select "All" as an option in the lists and this will bring back all the records with any of the options from that list.

An example would be:

3 Fields: "Team" "Year" Brandname"

User selects Team = All (as opposed to selecting
a particular team)
year = 1998
Brandname = Topps

This should bring back all the baseball teams that are in the db for the year 1998 under the brandname Topps.

Thanks again in advance.

 

[kathryn]

OK, next question, how are you populating your boxes, from a query or from a manually entered list?

If it is a manually entered list, then make the first choice "All" and in your code, use an If statement so that if the value is "All", do not include the criteria for that field. Kathryn

 

[scking]

This is just one very simple implementation that needs a little work before it is production ready. You just need to append criteria. There are many, many ways to do this.

btnExecuteQuery_OnClick

Dim strSQL As String
strSQL = "SELECT * FROM tblWhatever WHERE"
If Not IsNull(cboCombo1) Then
strSQL = strSQL & " Param1=" & Me.cboCombo1
End If

If Not IsNull(cboCombo2) Then
strSQL = strSQL & " Param2=" & Me.cboCombo2
End If

If IsNull(cboCombo1) And IsNull(cboComb2) Then
strSQL = "SELECT * FROM tblWhatever"
End If


Steve King Growth follows a healthy professional curiosity