Parameter Query Criteria Syntax Issue

[debbieR]

Hi! This should be simple, but I'm an Access/Query newbie and can't figure out syntax to accomplish the following:
How would I change the criteria syntax to make it return ALL records with value >0 if they just click OK and don't enter a specific value?

PRICE SELL QTY (Parameter Query so it asks them for a value or they click OK to return all values) Current criteria used is:
Like "*" & [Enter QTY or Click OK to Display ALL ] & "*"

This works correctly EXCEPT I need to add "something" to the criteria code that it returns ALL records >0 as well as prompting for a value.
(it doesn't work to just put >0 unless I'm putting it in the wrong place)
Thank you!

 

[mole999]

I think you can use OR
something like
PRICE SELL QTY Like "*" & [Enter QTY or Click OK to Display ALL ] & "*" OR >0

not sure of the Access syntax

 

[debbieR]

Worth a try! Thank you!!

 

[debbieR]

Nope, didn't work

 

[debbieR]

Solved it!
Changed to: >0 Or [Enter QTY or Click OK to Display ALL ]
now if they don't enter a value and just click OK, all records with values > 0 are returned.

 

[debbieR]

My Bad...THIS one fixes the "zero" issue if they just click OK AND still displays the correct records if they enter a number:
>0 And Like "*" & [Enter QTY or Click OK to Display ALL ] & "*"

 

[Micron]

The * wildcard can only be used with text and it seems your field is numeric. Is that the case, or are you treating numbers as text? This may be a candidate for the IIF function. However, regardless if the field is numeric I don't think you can solve this in a query because I don't think you can use > in either the truepart or falsepart of IIF.
In that case, I think your query would need to call a function.

 

[debbieR]

I think you can use OR
something like
PRICE SELL QTY Like "*" & [Enter QTY or Click OK to Display ALL ] & "*" OR >0

not sure of the Access syntax

Thank you...you were very close. I had tried it on the back side and it didn't work (and it needed to be an AND not an OR). This is what worked:
>0 And Like "*" & [Enter QTY or Click OK to Display ALL ] & "*"

 

[Micron]

That is cool. Sometimes I can't see the forest for the trees .
Greater than zero and Like "*" if no response.
BTW, the first * isn't needed. You're getting ** for no response when all you need is *, which means I stand corrected. You can use * on numbers.
This also works >0 And Like [Enter QTY or Click OK to Display ALL ] & "*"

 

[debbieR]

The * wildcard can only be used with text and it seems your field is numeric. Is that the case, or are you treating numbers as text? This may be a candidate for the IIF function. However, regardless if the field is numeric I don't think you can solve this in a query because I don't think you can use > in either the truepart or falsepart of IIF.
In that case, I think your query would need to call a function.

it is a number field...and I did solve it, see below. Now returns exactly what I was looking for! Thank you for responding though! It took me lots of try's to get it to work so had I not your suggestion may have come in handy.