Filtering a Form with a Query

[Kyle]

I am trying to apply a filter to a form based on a query. I have four fields
that I would like the user to input from before applying the filter. I am
having difficulty getting the filter to display the desired result. The user
can input from 1 to 4 variables, in this case they are;

- Supplier Code
- Buyer#
- Requisitioner
- Status

So for example, the user could enter their Buyer# and select a Status
"Completed" from the drop down list to display the orders they have
completed, regardless of who the Requisitioner was or who it was placed with.

Another example may be selecting a Supplier Code from the drop down list to
display all the orders placed with a particular supplier, regardless of who
placed the order or its Status.

The unbound fields are displayed in a subform with an execute button to run
the macro. Those appear to be working fine. I am certain that there is
something wrong with the query I have created. I have tried using this
syntax for each of the variables in many different ways in the query...

[Forms]![frmQBF]![Buyer Assigned] or Is Null

I'm stumped. Any suggestions would be greatly appreciated.

Thanks, Kyle

 

[Golfinray]

Google "cascading combo boxes"

 

[Kyle]

You are correct. Here is what I tried and got to work.

Like "*" & [Forms]![frmQBF]![Buyer Assigned] & "*" Or Is Null

Thanks for your help.

[Forms]![frmQBF]![Buyer Assigned] or Is Null

I assume that line is in your criteria of the query for each of the four
fields represented by the combo boxes. And I assume that a combo box can
have a value, or be left blank.

If my assumptions are correct, I don't think "or Is Null" is what you mean.
Do you really mean that if the combo box is null, don't apply any criteria at
all? Because if that is what you mean, then you are indeed applying a
criteria of "Is Null".

If I'm on target so far, try this:

IIf(IsNull([Forms]![frmQBF]![Buyer Assigned]),"*",[Forms]![frmQBF]![Buyer
Assigned])

or

nz([Forms]![frmQBF]![Buyer Assigned],"*")

or something like that.

I am trying to apply a filter to a form based on a query. I have four fields
that I would like the user to input from before applying the filter. I am
having difficulty getting the filter to display the desired result. The user
can input from 1 to 4 variables, in this case they are;

- Supplier Code
- Buyer#
- Requisitioner
- Status

So for example, the user could enter their Buyer# and select a Status
"Completed" from the drop down list to display the orders they have
completed, regardless of who the Requisitioner was or who it was placed with.

Another example may be selecting a Supplier Code from the drop down list to
display all the orders placed with a particular supplier, regardless of who
placed the order or its Status.

The unbound fields are displayed in a subform with an execute button to run
the macro. Those appear to be working fine. I am certain that there is
something wrong with the query I have created. I have tried using this
syntax for each of the variables in many different ways in the query...

[Forms]![frmQBF]![Buyer Assigned] or Is Null

I'm stumped. Any suggestions would be greatly appreciated.

Thanks, Kyle