IIF doesn't work when using operators in truepart/falsepart

[MatthewGrace]

To circumvent the need to write SQL, I'm simply using the "criteria" in Query Design view:

Using this Expression is fine... Everything runs exactly as you'd expect, you'll see records where ID = 20 or ID = 23, depending on the value of [variable]:


But try adding some simple operators and Access throws a fit:




I've tried getting rid of the quotes, replacing them with single quotes, neither work.
Thank you to anyone who offers their thoughts.

[CJ_London]

you can't write it that way -

you need

(<20 and [variable]=1) OR (>20 and [variable]<>1)

[orange]

Matthew,

I think this is one of those cases where, if you described exactly what you are trying to accomplish in plain English, then you would get some answers and options.
You are showing us HOW you tried to do something that doesn't work.
Perhaps Ajax has guessed correctly, but until we know WHAT you are trying to do, we can't offer answers without guessing at your intentions.

iif()

[MatthewGrace]

I tried to distill the concept down as much as I could and divorce it from my situation in hopes that it would make it easier for people to comment. The fullness of my situation looks more like this:

I have a Query with an Orders Table, having a field called Status (values are 'Pending', 'In Progress', 'Ready', 'Shipped'). In the Status Field Where condition I have:
IIF(Forms![frmOrderManager]![chkHideShipped]=true, <>'Shipped', "")
The goal here is - If a person puts a check in a box it does not show shipped orders.

This kind of statement I am learning won't work, because the "Truepart" and "Falsepart" of the IIf() only accepts fixed values (or so I think):.
Values like this work: "Shipped" ... "Yes" ... "True" ... 100
Values like this don't work: <>"Shipped" ... >100 ... <=20
So you see, no operators allowed with IIf().

Ajax was right in his answer, and I'm grateful for it, but I'd still like to know if there is a way to get IIf to accept operators in the truepart/falsepart area.

Thanks for the input guys.

[orange]

Matthew,

It seems that your database application involves Orders and Delivery/Shipments. These applications usually include Customers and Products; and often some sort of Payment information. Your description is specific to IIF, so it is hard to put the question into context.

IIf( someCondition, trueResult, falseResult) is the basic syntax.

Your example

IIF(Forms![frmOrderManager]![chkHideShipped]=true, <>'Shipped', "")

is a little confusing.
If you are working with variables/controls on a form, you would normally refer to the form as Me.
Then the control would be Me.[chkHideShipped]

What exactly did you mean (plain English) with this<>'Shipped', ""?

I understand this

IIF(Forms![frmOrderManager]![chkHideShipped]=true,

to mean

If the checkbox chkHideShipped is true (or checked) then....???

[InsuranceGuy]

Values like this work: "Shipped" ... "Yes" ... "True" ... 100
Values like this don't work: <>"Shipped" ... >100 ... <=20
So you see, no operators allowed with IIf().

Ajax was right in his answer, and I'm grateful for it, but I'd still like to know if there is a way to get IIf to accept operators in the truepart/falsepart area.

Thanks for the input guys.

Sorry, the outcome of an IIF statement cannot include an operator because once you insert an operator into the IIF, it is actually outputting a String or Variant. The fields are then being tested for an equal comparison to the entirety of the IIF statement's output. Ajax's approach really is best.


Offtopic---

There are some fun things you can do with IIFs and Strings in LIKE comparisons. I use these frequently for filters because I like "Any" to be an option in my filter lists.

Given a string column [strFruit], you can do this kind of thing:

[strFruit] like iif([Forms]![frmSample]![lstFilter]="Any","*",[Forms]![frmSample]![lstFilter])

Cheers,

Jeff