I'm having trouble with an IIF statement in a Query "Where" Clause.


I can demonstrate with the sample NorthWind database, using the ID field.
Firstly I said, "Show me numbers 3,4,5,6."

Code:
SELECT Employees.EmployeeID
FROM Employees
WHERE (((Employees.EmployeeID) Between 3 And 6));

That returned the expected four rows.

Then I said "If my condition is False, show me 3,4,5,6, otherwise show row
8."

Code:
SELECT Employees.EmployeeID
FROM Employees
WHERE (((Employees.EmployeeID)=IIf(False,(Employees.Empl oyeeID) Between 3
And 6,8)));

That returned the single row 8, as expected.

I then changed the condition from False to True, expecting 4 rows.

Code:
SELECT Employees.EmployeeID
FROM Employees
WHERE (((Employees.EmployeeID)=IIf(True,(Employees.Emplo yeeID) Between 3
And 6,8)));

Bugger. No rows.

Can anyone indicate what my error is?
(Obviously my actual query is a little more useful.)

On Thu, 05 Jul 2012 21:23:53 -0500, Alan Carpenter <Not (AT) iHome (DOT) nz>
wrote:

[snip]

The true form works out to
where Employees.EmployeeID=Employees.EmployeeID between 3 and 6
whatever that means. Try something like:
select Employees.EmployeeID from Employees
where
((condition) and Employees.EmployeeID between 3 and 6)
or
(not (condition) and Employees.EmployeeID=8)

Sincerely,

Gene Wirchenko

Gene Wirchenko <genew (AT) ocis (DOT) net> wrote in
news:5gocv7doe9k9q4qjos0921oahoeupruv49 (AT) 4ax (DOT) com:

Thanks, Gene, but you've misinterpreted the problem.

Gene Wirchenko <genew (AT) ocis (DOT) net> wrote in
news:5gocv7doe9k9q4qjos0921oahoeupruv49 (AT) 4ax (DOT) com:

I should perhaps explain my example (which is only an example, not a real
world case).

The Employee table in the sample NorthWind database contains 9 entries.

I demonstrated with an arbitrary set of 3,4,5,6 as the case I wanted, with
an arbitrary case of 8 for the false case.

Using a Where clause of
where Employees.EmployeeID=Employees.EmployeeID between 3 and 6
will return the rows 3,4,5,6

This is what I expect, and it works as I expect.

However, if I use that identical statement as the True condition in an IIF
statement, it returns no rows.
IIf(True,(Employees.EmployeeID) Between 3 And 6,8)

By specifying a condition "True" I'm substituting for "a condition that
evaluates to "True"" just as a test. eg:

Debug.? IIF(True,"Yes","No") = Yes
Debug.? IIF(False,"Yes","No") = No

I'd be satisfied if someone could point me to documentation which says I
can't use IIF in a query's criteria, in which case I'd just create an "on
the fly" query from code.
Not happy, but satisfied.

It's syntax Alan.

You can't have "= Between..."

Try:

SELECT Employees.EmployeeID
FROM Employees
WHERE IIf(True,(Employees.EmployeeID) Between 3
And 6,(Employees.EmployeeID)=8);

HTH

Jon

"Alan Carpenter" <Not (AT) iHome (DOT) nz> wrote

"Jon Lewis" <jon.lewis (AT) GETRIDOFTHISBITbtinternet (DOT) com> wrote in
news:K4mdnaOFYsuaKGvSnZ2dnUVZ8tednZ2d (AT) bt (DOT) com:

Bugger.

My first reaction was, "But I didn't say '= Between'!"

Looking more closely at what actually happens during evaluation, though, I
see what I missed.

100% correct, and my actual query works perfectly.

Thanks, Jon. Take the rest of the day off.

Ha ha!

I wish I could!


"Alan Carpenter" <Not (AT) iHome (DOT) nz> wrote