Optional Query Parameters help! (1 Viewer)

[Denyial]

I posted a question yesterday however realised in hindsight that I wasn't particularly clear with what I needed help with.

I am looking to input data from two text boxes on a form, indicating a price range and have a query show all records with values between those numerical values.
Normally I would write something along the lines of:

Between [Forms]![formName]![inputboxName1] AND [Forms]![formName]![inputboxName2]
in the criteria box, thereby giving me the values between box 1 and box 2, however this makes it that the criteria is necessary for the query, and if the user chose not to input a value into box 1 or box 2, the query outputs a blank table as the input was essentially nothing.

How can I change my criteria so that I can get an optional query parameter giving records in the range of box 1 and box 2?

If you need any more information I'm happy to get it, sorry I'm new!

Thanks!

 

[Ranman256]

check the text box before you open the query

if isnull(inputBoxName1) then
   docmd.openquery "qsQryNoParams"
else
   docmd.openquery "qsQryDateRange"
endif

 

[Denyial]

I'm sorry I don't understand what you mean. I tried copying the code you sent me into the query criteria box, however I got an 'incorrect syntax' error...

 

[jdraw]

??
If 1 of the parameters is optional, what value do you intend to use to get the data/records you need?
I think you need some logic to assign a default value if no value is entered.

You need to think of the conditions and what you want to happen

If box1 is not valued---do this
If box2 is not valued---do this
If box1 and box2 are not valued--- do this

 

[Denyial]

If box 1 and/or box 2 is not valued, I was hoping that it would return every record possible so that the other criteria (e.g Location, Date etc) would refine the table.

If they were both valued, it then outputs the fields corresponding to their range, as well as other criteria listed.

For additional optional criteria, I've used the code: Like [Forms]![frmProject Query].[txtProjectRegion] & "*" . This allows for an optional input box (named txtProjectRegion). If I choose to leave that blank, it simply returns all records.

What now?

 

[jdraw]

Once you determine exactly what you want to happen based on the conditions(values in box1 and/or box2), you would adjust your SQL criteria accordingly.

Let's say
...Where YourDate
Between Box1 and Box2 (no change if box1 and box2 are both valued) or

YourDate >= Box1 (if box2 is not valued)

YourDate <= Box2 (in box1 is not valued)

 

[Ranman256]

this is not criteria,
when you click the button to open the query.