DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: Access 2002 iif function bug?

  1. #1
    Farli Guest

    Access 2002 iif function bug?


    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.


  2. #2
    Douglas J. Steele Guest

    Re: Access 2002 iif function bug?

    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.
    >




Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center