How to use operators (< = >) within IIF truepart or falsepart??

[Micron]

A poster on another forum wanted to query a numeric field using the input from a parameter prompt OR > 0 if there was no input. I tried every iteration of an IIF expression falsepart that I could think of only to find that it would not properly evaluate > 0 or = 0 for example.
A sample would be IIf([Enter Value] Is Null,>2,[Enter Value]) . Result is 0.
I tried
- Eval on various forms of > (quotes, no quotes, etc); result was sometimes an error
- the wildcard * (thought these could not be used on numbers) e.g. Like IIf([Enter Value] Is Null,"*",[Enter Value]) - that's just one.
- tried Nz;
IIf(Nz([Enter Value],0)=0,(>1),[Enter Value])
IIF(Nz([Enter Value],0),>1,[Enter Value])
IIF(Nz([Enter Value],0) = 0,>1,[Enter Value])

Nothing worked. With no input, I either got no records, 0 (see blue above), or an error. So this is a two part post I guess.


Part 1 - does anyone know if operators can be used in truepart or falsepart of IIF? I searched long and hard but found nothing.

Part 2 - the eventual solution (regrettably not mine) to the problem. It's so simple that I will now fold up my laptop and go get some beer, because I'm a dummy.
I will leave that for a bit to see if anyone wants to take a whack at it - and no cheating!

BTW, regarding the Nz function in query criteria, I found this interesting:
http://allenbrowne.com/QueryPerfIssue.html - specifically the IIf(), not Nz() paragraphs.

[kd2017]

Part 1 - I dunno, I'm just a rookie. I'd like to know the answer though.

Part 2 - Oh okay, so this is a challenge
Is this in a where clause? If so this doesn't really answer your original question but seems to get the results that I think they were after.

WHERE field=IIF( [Enter Value] is null, null, [Enter Value] )
OR field>IIF( [Enter Value] is null, 0, null )

When the [Enter Value] is null the query will return all values greater than 0. When it has a number it will only return matching values. I also noted that when a field in the table is null that it is never returned.
Is that anything like the eventual solution you spoke of?

[June7]

As for 1, I believe the answer is no, the operator cannot be dynamic.

[Micron]

the operator cannot be dynamic.

Sadly, this appears to be true, but even for just =, which I'm not sure I'd classify as dynamic. That's the crux of the question I had.
As for the solution, thanks kd2017. Nice of you to provide that, which helps me with my all too frequent stumbling over OR and AND sometimes. Your solution does work when IIf([Enter Value] Is Null,Null,[Enter Value]) Or >IIf([Enter Value] Is Null,0,Null) is entered into the criteria field of query design. The solution was a bit simpler: > 0 And Like [Enter Value] & "*". I'm surprised * works with numbers - at least it does with long integers.

[June7]

LIKE and wildcard will work with numbers but can produce undesired results. Say you have a query with multiple dynamic parameters and use LIKE and wildcard on each so user has option to not provide a parameter. Even though LIKE is used the desire is really to match a very specific criteria when value is entered (or selected via a combobox). If number field has range of numbers that have varying number of digits, say 1 to 1000, if user enters 10 when the criteria is LIKE [fieldname] & "*" there will be a match to values 10, 100 ... 109, 1000.

I have NEVER used dynamic parameterized queries.