Comparators don't work inside an IIF return in a where clause?

[MrBologna]

I'm writing a query that compares search form input values to table values.


There are quite a few IIF statements in the WHERE clause, mostly in this form for checkboxes:

IIf([forms]![Job_Reference_Form]![Conveyor] Is Null,([Job_Reference].[Conveyor])=True Or ([Job_Reference].[Conveyor]) Is Null,[forms]![Job_Reference_Form]![Conveyor])

This works very well for my triple-state checkboxes, and my syntax for searching partial matches on text fields works very well too.

The problem occurs when I want to look at number fields. I would like something along these lines:

IIf(IsNull([Forms]![Job_Reference_Form]![Floats_Qty]),([Job_Reference].[Floats_Qty])>=0 Or Is Null,([Job_Reference].[Floats_Qty])>=[Forms]![Job_Reference_Form]![Floats_Qty])

HOWEVER, this does not return any results if I use any sort of comparator in the iif statement.

all of the following statements work as expected:

>=1 or is null
>=[Forms]![Job_Reference_Form]![Floats_Qty] Or Is Null (only returns null values when the form input is null)
IIf(IsNull([Forms]![Job_Reference_Form]![Floats_Qty]),1,2)

Am I just missing something, or is there a limitation on comparators in an IIF statement inside a WHERE clause?
The table is stored in SQL server if that makes a difference here.

[ranman256]

'xxx is null ' is for query criteria,
in formulas and all else , use: ISNULL(xxx)

Code:

IIf(isnull([forms]![Job_Reference_Form]![Conveyor]),...

[MrBologna]

I appreciate the feedback on my syntax and I will work on updating my checkbox clauses. However, that first statement works as intended.

This also returns no records:

IIf(IsNull([Forms]![Job_Reference_Form]![Floats_Qty]),([Job_Reference].[Floats_Qty])>=0,([Job_Reference].[Floats_Qty])>=[Forms]![Job_Reference_Form]![Floats_Qty])

I am confident that my issue lies with the comparators / returns of the IIF statement. changing to formulas with no comparators works as intended, and removing the comparators from the IIF works as intended.
I suppose I could break them out and do a SQL OR based on the value of the form input, but it will make my statement even longer than it already is as I will have to do a new OR for every combination of which int fields are null...

[Micron]

To me, the True/False portions are wrong. This [Job_Reference].[Floats_Qty]) likely has a value (or at least it depends on one). Say it is 5. Then 5 is >=0 so it will return True, not 5 and not 0 (except that False is also 0 and True is anything else). In other words it looks to me like you expect numeric values based on those 2nd and 3rd tests but you will only get T or F or their numeric representations. You probably need nested IIF's. Not possible for me to be specific based on your posts but along the lines of

IIF(TEST2,True part,IIF(TEST1,True part,False part),False part)

Could also drop the last false part if need be, I suppose. Key thing would be that the T F tests are not tests themselves unless you wrap each of those tests in IIF.

[isladogs]

I really don't understand what your IIf statement means. As you know the syntax is IIf(expression, truepart, falsepart)
So using the code in post #3, you are saying that if the value of Floats_Qty control on your form is null then that field in the specified table should be greater than or equal to zero. (But what if it isn't...?)
Otherwise the table field value must be greater or equal to the form control value (But what if it isn't)

However neither the true part or false part can work in this context. Each must be a clearly defined value so that Access can assign that value if the expression is true /false.
Your examples at the end of post #1 work fine because they match those requirements.
But any time you include a comparator, its not a precise value
So IMHO you cannot use comparators in IIf statements as indicated in your posts.
And I believe that to try and use them in WHERE clauses makes it even more nonsensical a proposition.

Similarly, the first expression in post #1 makes no sense to me whether its a dual state bound checkbox (true/false only) or for a triple state checkbox (true/false/null) which can only be done in Access for unbound checkboxes.

[CJ_London]

Suggest provide your sql, some example data and the required result since you cannot use comparators within an iif clause in sql

[MrBologna]

To me, the True/False portions are wrong. This [Job_Reference].[Floats_Qty]) likely has a value (or at least it depends on one). Say it is 5. Then 5 is >=0 so it will return True, not 5 and not 0 (except that False is also 0 and True is anything else). In other words it looks to me like you expect numeric values based on those 2nd and 3rd tests but you will only get T or F or their numeric representations. You probably need nested IIF's. Not possible for me to be specific based on your posts but along the lines of

IIF(TEST2,True part,IIF(TEST1,True part,False part),False part)

Could also drop the last false part if need be, I suppose. Key thing would be that the T F tests are not tests themselves unless you wrap each of those tests in IIF.

I think you're right! The reason my syntax for the checkboxes works is because the return of True or False just happens to match the value of the checkbox
If we take the following example SQL:

SELECT Job_Reference.Floats_Qty
FROM Job_Reference
WHERE (((Job_Reference.Floats_Qty)=IIf(IsNull([Forms]![Job_Reference_Form]![Floats_Qty]),(Job_Reference.Floats_Qty)>=0,(Job_Reference.Floats_Qty)>=[Forms]![Job_Reference_Form]![Floats_Qty])));

Lets say my form input is null. Upon execution, my WHERE clause becomes:

WHERE (((Job_Reference.Floats_Qty)=(Job_Reference.Floats_Qty)>=0)));

Which describes my issue! floats_quantity=true will return nothing, thank you

Now, however, I have a new issue. How do I return all values in SQL on an int?
My new WHERE clause of:

IIf((IsNull([Forms]![Job_Reference_Form]![Floats_Qty])),([Job_Reference].[Floats_Qty]),Null)

works for the most part, but does not return null values (when the IIF is true. I am ignoring the false return for now) and putting an "or is null" into my true return breaks it:

WHERE (((Job_Reference.Floats_Qty)=IIf((IsNull([Forms]![Job_Reference_Form]![Floats_Qty])),(Job_Reference.Floats_Qty)=([Job_Reference].[Floats_Qty]) Or ([Job_Reference].[Floats_Qty]) Is Null,Null)));

upon execution, I think it should turn into the following valid (tested) statement:

WHERE (((Job_Reference.Floats_Qty)=([Job_Reference].[Floats_Qty]) Or (Job_Reference.Floats_Qty) Is Null));

however it does not return any values. Thoughts?

sorry this is now pretty convoluted and confusing. I am currently playing around with the NZ() function and some other solutions. You have answered my question well enough

[MrBologna]

For some clarification. The logic behind the IIF satement:

If [Form Input] Is Null
     Return all values AND nulls
Else (assume [Form input] is a positive int)
     Return all values Greater than [Form Input], AND nulls.

and I've attached a sample database as well. TestDB.zip

please note this is a heavily edited version of my data and some features will not work.

I suppose defaulting everything that's null to a 0 and making the query just ">=[Form Input]" would work.

[isladogs]

As already explained, that logic won't work in an IIf statement
In addition, you cannot assume anything in the Else (False) part - the Form Input value will be any not null vale

It is possible to use nested IIf statements to allow for e.g. Null, >0, 0, <0 but in each of these, the true part & false part MUST be specific values

NOTE: I haven't looked at your database and don't intend to spend time discovering what does/doesn't work..
Please be specific about what anyone opening it should look at. Make sure that does work & is relevant to this thread

[CJ_London]

would think your logic

If [Form Input] Is Null
Return all values AND nulls
Else (assume [Form input] is a positive int)
Return all values Greater than [Form Input], AND nulls.

would translate to

WHERE somefield>nz(forminput,somefield-1) OR somefield is null

[June7]

Review https://stackoverflow.com/questions/...y-returns-true

[MrBologna]

would think your logic




would translate to

WHERE somefield>nz(forminput,somefield-1) OR somefield is null

This is exactly what I needed. Just got a little too caught up in the IIF. Thank you for your time!!

[CJ_London]

would question this part

somefield>nz(forminput,somefield-1)

if the user enters 5, you will only return 6 and above. Is that correct? If you want 5 and above then you would use

somefield>=nz(forminput,somefield)