ACC: How to Use the IIf() (Immediate If) Function

 Last reviewed: May 14, 1997
Article ID: Q106288
The information in this article applies to:
  • Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

You may receive unexpected results when you are using the IIf() (Immediate If) function to evaluate an expression. Your results will depend on the use of double quotation marks in the function's arguments.

MORE INFORMATION

The following example demonstrates how the use of double quotation marks in the IIf() function's arguments can affect the results:

  1. Open the sample database Northwind.mdb (or NWIND.MDB in version 1.x or 2.0)

  2. Create the following new query based on the Orders table: 

          Query: Test
      ---------------------------------------
      Field Name: Sum(IIf("[Freight]>50",1,0))

  3. Run the query. The result will be the total number of records in the table, not the number of records with Freight charges greater than 50.

  4. View the query in Design view. Remove the double quotation marks so the query matches the following: 

          Query: Test
      -------------------------------------
      Field Name: Sum(IIf([Freight]>50,1,0))

  5. Run the query again. The result will be the correct number of records where the freight charge amount is greater than 50.

The double quotation marks around the "[Freight] > 50" argument cause Microsoft Access to evaluate the expression as a string instead of as a comparison. A string will always evaluate to true, while a comparison will test for the condition to be met before returning a value.

REFERENCES

For more information about the IIf function, search for "IIf function," and then "IIf Function" using the Microsoft Access 97 Help Index.

Keywords : ExrOthr kbusage
Version : 1.0 1.1 2.0 7.0 97
Platform : WINDOWS
Hardware : X86
Issue type : kbhowto

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: May 14, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.