Access 2002 iif function bug?

[Farli]

Hi All,
I am stuck on the following and am wondering if it is actually a bug. If
anyone has a suggestion of a better way to go about it, please let me know.

-I have a combo control on a form, I want it to limit the results of a query
when a name is selected on the form, otherwise if the form is left blank,
just display all results.

The criteria I am entering on the query for the field is:
IIf([Forms]![Review_ClientNotes]![Selection1] Is Null,>0,[Forms]![Review_ClientNotes]![Selection1])

What works:
1) The IIF statement performs the logical operation (IE can read the form,
accurately determine null vs non-null and go to the appropriate next step)
2) Selecting a name on the form displays records for that name only.
3) The IIF statement will work if I put a specific Name (links to a number)
in the true segment like so:
IIf([Forms]![Review_ClientNotes]![Selection1] Is Null,3,[Forms]![Review_ClientNotes]![Selection1])
4) REPLACING the whole if statement with a wildcard such as >0, * displays
all records.

What Doesn't:
The IIF statement will NOT work if I put a wildcard in the true segment even
though these same wildcards work without the iif statement.
(Eg...
IIf([Forms]![Review_ClientNotes]![Selection1] Is Null,>0,[Forms]![Review_ClientNotes]![Selection1])

Any ideas??

Thanks in advance.

[Douglas J. Steele]

It's not a bug: it wasn't designed to do what you're trying to do.

First of all, you should be using
IsNull([Forms]![Review_ClientNotes]![Selection1]) rather than
[Forms]![Review_ClientNotes]![Selection1] Is Null

The real problem, though, is that when you put the IIf statement as a
criteria, the SQL that's generated is something like:

WHERE MyField = IIf([Forms]![Review_ClientNotes]![Selection1] Is
Null,>0,[Forms]![Review_ClientNotes]![Selection1])

What you want is

WHERE MyField = [Forms]![Review_ClientNotes]![Selection1] OR
IsNull([Forms]![Review_ClientNotes]![Selection1])



--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele


"Farli" <q5nj1woocef001@sneakemail.com> wrote in message
news:3d21223c$1@10.1.10.29...
>
> Hi All,
> I am stuck on the following and am wondering if it is actually a bug. If
> anyone has a suggestion of a better way to go about it, please let me
know.
>
> -I have a combo control on a form, I want it to limit the results of a
query
> when a name is selected on the form, otherwise if the form is left blank,
> just display all results.
>
> The criteria I am entering on the query for the field is:
> IIf([Forms]![Review_ClientNotes]![Selection1] Is
Null,>0,[Forms]![Review_ClientNotes]![Selection1])
>
> What works:
> 1) The IIF statement performs the logical operation (IE can read the form,
> accurately determine null vs non-null and go to the appropriate next step)
> 2) Selecting a name on the form displays records for that name only.
> 3) The IIF statement will work if I put a specific Name (links to a
number)
> in the true segment like so:
> IIf([Forms]![Review_ClientNotes]![Selection1] Is
Null,3,[Forms]![Review_ClientNotes]![Selection1])
> 4) REPLACING the whole if statement with a wildcard such as >0, * displays
> all records.
>
> What Doesn't:
> The IIF statement will NOT work if I put a wildcard in the true segment
even
> though these same wildcards work without the iif statement.
> (Eg...
> IIf([Forms]![Review_ClientNotes]![Selection1] Is
Null,>0,[Forms]![Review_ClientNotes]![Selection1])
>
> Any ideas??
>
> Thanks in advance.
>