CODEWITHSUNDEEP
sqlms-accessms-access-2010
jhalf2008
jhalf2008

Reputation: 45

Access Query SQL IIf Statement with OR

I have the following SQL statement:

SELECT * FROM BOM INNER JOIN Parts ON BOM.PartID = Parts.PartID
WHERE (((BOM.PUMP_CODE) Like (IIf([Forms]![frmSelector]![tbMotor]>0 And
     [Forms]![frmSelector]![tbShaft]>0,"MOTOR" Or "SHAFT",IIf([Forms]![frmSelector]![tbMotor]>0,"MOTOR","SHAFT")))))
ORDER BY BOM.BOM_INDEX;

In the first IIF statement, I cannot get the query to return any records. I want it to return all records with either "MOTOR" or "SHAFT" in the PUMP_CODE field when both referenced objects are > 0. Even though both objects are greater than 0, it still does not return any records. The latter IIF statement is working just fine however. Is there a problem with this query?

Upvotes: 2

Views: 8876

Answers (2)

dbmitch
dbmitch

Reputation: 5386

Try using the IN Clause instead of LIKE and change your IIF to put both together if both settings are greater than 0, otherwise use your working clause.

I also added escaped double quotes to enclose the pair, and added to single items in case you ever use items that have spaces in them

SELECT * FROM BOM INNER JOIN Parts ON BOM.PartID = Parts.PartID
WHERE BOM.PUMP_CODE IN (
IIf([Forms]![frmSelector]![tbMotor]>0 And [Forms]![frmSelector]![tbShaft]>0, """MOTOR"",""SHAFT""",
     IIf([Forms]![frmSelector]![tbMotor]>0,""MOTOR"",""SHAFT"")))     
ORDER BY BOM.BOM_INDEX;

Upvotes: 0

Marc B
Marc B

Reputation: 360882

Your "MOTOR" Or "SHAFT" is wrong. The or of two strings is evaluated FIRST, and the result of that or is what gets used by iif().

That means you're effectively doing

IIF(..., true, ...)

or whatever it is that access ors two strings into.

In any pretty much ANY programming language, you cannot do

if (x == "a" or "b")

because it'll be executed as

if (x == ("a" or "b")) -> if (x == (result))

You need to do

if (x == "a") OR (x == "b")

Upvotes: 2

Related Questions