QBF with Multiple Criteria for Some Variables (1 Viewer)

[ed coleman]

I am trying to produce a form where someone can enter data that will retrieve records that match the criteria. The form will have many different variables, and if some are left blank, I want the query to show all applicable records for the overall search.

So far, I have created a form that has a combo box for the grade and flute for cardboard consumed and a low and high with for the length of the board and the same for the width.

If I do not put anything in the grade and flute box, and using the formula in the query for this form control as well as an ISNULL(for this form control), the query returns all the records that suit the input for size and width without any entry of the grade and flute. This is working the way it should.

However, I am using a "Between Low and High" formula for both length and width and can't get the ISNULL set up for the multiple criteria. This means that it only works when I have sizes input into the length and width on the form. This is not the way it should work. These measurement variables can be left blank as well and not affect what records are retrieved.

I am going to attach two files that shows the QBF form thus far as well and the query criteria for the length and width.

Any assistance would be much appreciated.

 

[pbaldy]

I personally lean towards dynamic SQL in these situations, but try

Between Nz(FormReference, 0) And Nz(FormReference, 999)

using appropriate values.

 

[ed coleman]

Thanks Paul. It worked like a charm.

 

[pbaldy]

Happy to help, and welcome to the site by the way!