Passing Query parameters through a Form

[hyperpau]

Hi there guys!

I have a Form where there are three comboboxes. This comboboxes are used as references for the parameter of 3 fields in a query. when I hit a command button in my form, it opens a query with the parameters selected from the form.

Let's say I have three fields in my query. [Description],[Status], and [Location]
On the design view of the query, I put these criterias on different lines so that it would be using the OR:

[Description]
=[Forms]![frmMyForm]![cboDescription]

[Status]
=[Forms]![frmMyForm]![cboStatus]

[Location]
=[Forms]![frmMyForm]![cboLocation]


This works fine even if I leave the other combo boxes in my form as blank. But the Problem is, if a make a selection for all combo boxes in my form, my query shows all the records with matching results from the different fields.

What I want to do is that when selections are made to all three comboboxes, the query would only show records that matches all the fields.

I tried putting all the criterias of the query on just one line. But then it would not show any records if no selection is made on any one of the comboboxes.

Can any one help me on this?

What I want is that, when I only make a selection to only one combo box, then the query results will give me all records that matches that combo box.
But If I make selection to more than one combo box, then it should give me results with both fields matching the parameters passed over by the comboboxes.

Meaning:
If only one combo box is changed, the parameters would be using OR
If two or all combo boxes are changed, the parameters would be using AND

[Rabbit]

Expr1: Nz(FieldName, "")
=Like "*" & Forms!FormName!cboName & "*"

Use AND instead of OR

[Bluejay906]

The SQL should be:

SELECT * FROM tblTable WHERE [Description]=Me.cboDescription AND [Status]=Me.cboStatus AND [Location]=Me.cboLocation

Because the conditions are joined by ANDs, only the records that match all three conditions will be displayed. Now, if you are displaying the information with a form or report, place this is the WHERE portion of the DoCmd:

"WHERE [Description]=' " & Me.cboDescription & " ' AND [Status]=' " & Me.cboStatus & " ' AND [Location]=' " & Me.cboLocation & " ' "

(I have assumed that the values are text and not numeric for the combo boxes. If the values are numeric, you can drop the " ' "s. Example: " AND [Location]=" & Me.cboLocation.

Hi there guys!

I have a Form where there are three comboboxes. This comboboxes are used as references for the parameter of 3 fields in a query. when I hit a command button in my form, it opens a query with the parameters selected from the form.

Let's say I have three fields in my query. [Description],[Status], and [Location]
On the design view of the query, I put these criterias on different lines so that it would be using the OR:

[Description]
=[Forms]![frmMyForm]![cboDescription]

[Status]
=[Forms]![frmMyForm]![cboStatus]

[Location]
=[Forms]![frmMyForm]![cboLocation]


This works fine even if I leave the other combo boxes in my form as blank. But the Problem is, if a make a selection for all combo boxes in my form, my query shows all the records with matching results from the different fields.

What I want to do is that when selections are made to all three comboboxes, the query would only show records that matches all the fields.

I tried putting all the criterias of the query on just one line. But then it would not show any records if no selection is made on any one of the comboboxes.

Can any one help me on this?

What I want is that, when I only make a selection to only one combo box, then the query results will give me all records that matches that combo box.
But If I make selection to more than one combo box, then it should give me results with both fields matching the parameters passed over by the comboboxes.

Meaning:
If only one combo box is changed, the parameters would be using OR
If two or all combo boxes are changed, the parameters would be using AND

[hyperpau]

Expr1: Nz(FieldName, "")
=Like "*" & Forms!FormName!cboName & "*"

Use AND instead of OR

This works perfectly. Thanks.