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.

 

[Pat Hartman]

One method of using optional criteria in a querydef:

Select ...
From ...
Where (fld1 = Forms!yourform!yourcontrol OR Forms!yourform!yourcontrol is null) AND ( fld2 = Forms!yourform!yourcontrol2 OR Forms!yourcontrol2 Is Null) AND ...

The point of the parentheses Is to control the order of execution since AND takes precedence over OR. But parentheses take precedence over AND. My example shows how to use multiple conditional criteria. For a single one, you can omit the parentheses. Just keep the concept in mind because whenever a complex expression contains OR and AND, you will almost certainly need to control how the expression should be evaluated since:

A OR B AND C is evaluated as:
A OR (B AND C) but you might mean:
(A OR B) AND C