Using IIf condition in Query Builder

[jtertin]

I am trying to have a query that populates a drop down list be
conditional on the value of a text box on my form (which is a 2 or a
1). I have tried the following two methods to try and accomplish
this, but get the error "Invalid SQL statement, expected DELETE,
INSERT, PROCEDURE...."

IIf(Forms![Recipe Configuration]!txtRecNo=2,
SELECT dbo_vw_UsedCodes.Code, dbo_vw_UsedCodes.ValueUnit,
dbo_vw_UsedCodes.CodeName
FROM UsedCodes INNER JOIN dbo_vw_UsedCodes ON UsedCodes.Code =
dbo_vw_UsedCodes.Code
WHERE dbo_vw_UsedCodes.SystemAvailability in (1,3)
ORDER BY dbo_vw_UsedCodes.Code,
SELECT dbo_vw_UsedCodes.Code, dbo_vw_UsedCodes.ValueUnit,
dbo_vw_UsedCodes.CodeName
FROM UsedCodes INNER JOIN dbo_vw_UsedCodes ON UsedCodes.Code =
dbo_vw_UsedCodes.Code
WHERE dbo_vw_UsedCodes.SystemAvailability in (2,3)
ORDER BY dbo_vw_UsedCodes.Code);

If Forms![Recipe Configuration]!txtRecNo=2
SELECT dbo_vw_UsedCodes.Code, dbo_vw_UsedCodes.ValueUnit,
dbo_vw_UsedCodes.CodeName
FROM UsedCodes INNER JOIN dbo_vw_UsedCodes ON UsedCodes.Code =
dbo_vw_UsedCodes.Code
WHERE dbo_vw_UsedCodes.SystemAvailability in (1,3)
ORDER BY dbo_vw_UsedCodes.Code;
ElseIf
SELECT dbo_vw_UsedCodes.Code, dbo_vw_UsedCodes.ValueUnit,
dbo_vw_UsedCodes.CodeName
FROM UsedCodes INNER JOIN dbo_vw_UsedCodes ON UsedCodes.Code =
dbo_vw_UsedCodes.Code
WHERE dbo_vw_UsedCodes.SystemAvailability in (2,3)
ORDER BY dbo_vw_UsedCodes.Code;

Thank you in advance for advice!

 

[Douglas J. Steele]

Where are you putting those IIf statements? It's not always possible to use
VBA functions in certain places in Access. If it is possible where you're
trying, though, you'll need quotes around the SQL:

IIf(Forms![Recipe Configuration]!txtRecNo=2, "SELECT dbo_vw_UsedCodes.Code,
dbo_vw_UsedCodes.ValueUnit, dbo_vw_UsedCodes.CodeName FROM UsedCodes INNER
JOIN dbo_vw_UsedCodes ON UsedCodes.Code = dbo_vw_UsedCodes.Code WHERE
dbo_vw_UsedCodes.SystemAvailability in (1,3) ORDER BY
dbo_vw_UsedCodes.Code", "SELECT dbo_vw_UsedCodes.Code,
dbo_vw_UsedCodes.ValueUnit,dbo_vw_UsedCodes.CodeName FROM UsedCodes INNER
JOIN dbo_vw_UsedCodes ON UsedCodes.Code = dbo_vw_UsedCodes.Code WHERE
dbo_vw_UsedCodes.SystemAvailability in (2,3) ORDER BY
dbo_vw_UsedCodes.Code")

 

[Bob Quintal]

I am trying to have a query that populates a drop down list be
conditional on the value of a text box on my form (which is a
2 or a 1). I have tried the following two methods to try and
accomplish this, but get the error "Invalid SQL statement,
expected DELETE, INSERT, PROCEDURE...."

IIf(Forms![Recipe Configuration]!txtRecNo=2,
SELECT dbo_vw_UsedCodes.Code, dbo_vw_UsedCodes.ValueUnit,
dbo_vw_UsedCodes.CodeName
FROM UsedCodes INNER JOIN dbo_vw_UsedCodes ON UsedCodes.Code
=
dbo_vw_UsedCodes.Code
WHERE dbo_vw_UsedCodes.SystemAvailability in (1,3)
ORDER BY dbo_vw_UsedCodes.Code,
SELECT dbo_vw_UsedCodes.Code, dbo_vw_UsedCodes.ValueUnit,
dbo_vw_UsedCodes.CodeName
FROM UsedCodes INNER JOIN dbo_vw_UsedCodes ON
UsedCodes.Code =
dbo_vw_UsedCodes.Code
WHERE dbo_vw_UsedCodes.SystemAvailability in (2,3)
ORDER BY dbo_vw_UsedCodes.Code);

If Forms![Recipe Configuration]!txtRecNo=2
SELECT dbo_vw_UsedCodes.Code, dbo_vw_UsedCodes.ValueUnit,
dbo_vw_UsedCodes.CodeName
FROM UsedCodes INNER JOIN dbo_vw_UsedCodes ON UsedCodes.Code
=
dbo_vw_UsedCodes.Code
WHERE dbo_vw_UsedCodes.SystemAvailability in (1,3)
ORDER BY dbo_vw_UsedCodes.Code;
ElseIf
SELECT dbo_vw_UsedCodes.Code, dbo_vw_UsedCodes.ValueUnit,
dbo_vw_UsedCodes.CodeName
FROM UsedCodes INNER JOIN dbo_vw_UsedCodes ON UsedCodes.Code
=
dbo_vw_UsedCodes.Code
WHERE dbo_vw_UsedCodes.SystemAvailability in (2,3)
ORDER BY dbo_vw_UsedCodes.Code;

Thank you in advance for advice!

you would be best served by putting the iff around the smallest
part of the sql that needs changing.

SELECT dbo_vw_UsedCodes.Code,
dbo_vw_UsedCodes.ValueUnit,
dbo_vw_UsedCodes.CodeName
FROM
UsedCodes
INNER JOIN
dbo_vw_UsedCodes
ON
UsedCodes.Code =
dbo_vw_UsedCodes.Code
WHERE
dbo_vw_UsedCodes.SystemAvailability
IN iif(Forms![Recipe Configuration]!txtRecNo=2,
(1,3),
(2,3)
)
ORDER BY
dbo_vw_UsedCodes.Code,