SQL syntax question

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

re: SQL syntax question

Hugh Welford wrote:[color=blue]
> 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[/color]

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

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

re: SQL syntax question

Thanks Bob - You are a genius - works fine now

Hugh


"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:%23BRNhs4rEHA.3520@TK2MSFTNGP11.phx.gbl...[color=blue]
> Hugh Welford wrote:[color=green]
> > 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[/color]
>
> 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[/color]
you[color=blue]
> 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[/color]
bring[color=blue]
> 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
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>[/color]