SQL syntax question

[Hugh Welford]

Hi - I am trying to include a conditional WHERE into an ACCESS query using
IIF, with the false-part a wildcard.

The expression SELECT details.patid, details.type FROM details WHERE
(((details.type)=IIf(1=1,2,(details.type) Like "*"))); returns all records
with type = 2, but

SELECT details.patid, details.typeFROM detailsWHERE
(((details.type)=IIf(1=2,2,(details.type) Like "*"))); returns NO records.

However, the unconditional expression SELECT details.patid, details.type
FROM details WHERE (((details.type) Like "*")); returns ALL records.

Can anyone help me with this?

Thanks Hugh

 

[Bob Barrows [MVP]]

Hi - I am trying to include a conditional WHERE into an ACCESS query
using IIF, with the false-part a wildcard.

The expression SELECT details.patid, details.type FROM details WHERE
(((details.type)=IIf(1=1,2,(details.type) Like "*"))); returns all
records with type = 2, but

SELECT details.patid, details.typeFROM detailsWHERE
(((details.type)=IIf(1=2,2,(details.type) Like "*"))); returns NO
records.

However, the unconditional expression SELECT details.patid,
details.type FROM details WHERE (((details.type) Like "*")); returns
ALL records.

Can anyone help me with this?

Thanks Hugh

IIF returns a value. It does not allow you to specify a statement. Let''s
look at your IIF statement:

IIf(1=2,2,(details.type) Like "*")

This will attempt to return "(details.type) Like "*"", but the fact that you
failed to delimit that string , and the quotes contained in that string,
should cause it to bomb. Open your database in Access. Press ctrl-g to bring
up the debug window, and paste

?IIf(1=2,2,(details.type) Like "*")

into the Immediate window. When you press Enter, you should get an error.

This should work better:

WHERE details.type = iif(1=2,2,details.type)

Bob Barrows

 

[Hugh Welford]

Thanks Bob - You are a genius - works fine now

Hugh