Using "IIF" to return ">0"

[pauliewalker5]

Hi Folks
It has been about 20 years since I have used Access so very rusty

I am trying to add a criteria field to a query as either, a single number selected on a form or, everything greater than 0.

Typing >=0 directly into the criteria field returns all the records (desired result). However, using IIf([Forms]![x]![xNum]="",>=0,[Forms]![x]![xNum) only returns results if a number is selected on the form

What I want is for the IIF statement to return >=0

Any help greatly appreciated

Paul Walker (neither fast nor furious)

 

[theDBguy]

Hi. Not sure it's clear to me yet what you want. If your table has data of -1, 0, 1, 2, 3, and the user enters 2 on the form, did you want your query to return 1, 2, and 3?

 

[CJ_London]

you cannot write your criteria this way

IIf([Forms]![x]![xNum]="",>=0,[Forms]![x]![xNum)

and usually a control will be null rather than a zls

usual way to write this is

=[Forms]![x]![xNum] or [Forms]![x]![xNum] is null

or

=iif([Forms]![x]![xNum] is null, [xNum], [Forms]![x]![xNum])

if you do have a potential zls in the control then instead of

[Forms]![x]![xNum] is null

use

nz(Forms]![x]![xNum],"")=""

 

[arnelgp]

SELECT * FROM yourTable
WHERE (((yourTable.TheFieldName)=Switch(IsNull([Forms]![x]![xNum]),[TheFieldName],True,[Forms]![x]![xNum])) AND ((Nz(,[TheFieldName],0))<>0));

 

[pauliewalker5]

you cannot write your criteria this way

IIf([Forms]![x]![xNum]="",>=0,[Forms]![x]![xNum)

and usually a control will be null rather than a zls

usual way to write this is

=[Forms]![x]![xNum] or [Forms]![x]![xNum] is null

or

=iif([Forms]![x]![xNum] is null, [xNum], [Forms]![x]![xNum])

if you do have a potential zls in the control then instead of

[Forms]![x]![xNum] is null

use

nz(Forms]![x]![xNum],"")=""

Hi CJ - thanks for your speedy response.

The 'unbound' control on the form passes the value to a text field hence I was using "".

I am more than happy to target the control directly as long as all records are displayed if nothing is selected on the control.

I have used Like "*" & [Forms]![x]![xNum] & "*" but this does not work as I want because regardless of whether my selection is 1 or 15 results from both are displayed - does this make sense??

 

[pauliewalker5]

Hi. Not sure it's clear to me yet what you want. If your table has data of -1, 0, 1, 2, 3, and the user enters 2 on the form, did you want your query to return 1, 2, and 3?

Hi DB Guy

So, My combolist has numbers ranging from 1 - 20.

Each number represents (for example) a car so 1 = Jeep, 2 = Ford, 3 = Ferrari, 15 = Porsche

If I select 1 from my combolist I want to see all of my Jeeps. If I select 2 I see all of my Fords. However, if I do not make a selection I want to see ALL the cars I have.

 

[arnelgp]

this demo is The Same Query in post #4.
but this is only a suggestion.

 

[pauliewalker5]

this demo is The Same Query in post #4.
but this is only a suggestion.

Ah, I can see what yo are doing with this and, although different to the way I have things set up, I think this might work - thank you, I will let you know

 

[pauliewalker5]

this demo is The Same Query in post #4.
but this is only a suggestion.

this demo is The Same Query in post #4.
but this is only a suggestion.

this demo is The Same Query in post #4.
but this is only a suggestion.

this demo is The Same Query in post #4.
but this is only a suggestion.

Hi arnelgp - I managed to use your example to achive exactly what I wanted. I am very rusty and, despite trying, could not have easily figured this out on my own - thank you so much.