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

Article translations Article translations
Article ID: 209192 - View products that this article applies to.
This article was previously published under Q209192
Novice: Requires knowledge of the user interface on single-user computers.

This article applies only to a Microsoft Access database (.mdb).

Expand all | Collapse all

SUMMARY

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

MORE INFORMATION

To illustrate how the use of double quotation marks in the IIf() function's arguments can affect the results, follow these steps:
  1. Start Microsoft Access and open the sample database Northwind.mdb.
  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 is 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 that the query matches the following:
    Query: Test
    -------------------------------------
    Field Name: Sum(IIf([Freight]>50,1,0))
  5. Run the query again. The result is the correct number of records where the freight charge amount is greater than 50.
The double quotation marks in the "[Freight] > 50" expression cause Microsoft Access to evaluate the expression as a string instead of as a comparison. A string always evaluates to True, whereas a comparison tests for the condition to be met before returning a value.

REFERENCES

For more information about the IIf function, click Microsoft Access Help on the Help menu, type iif function example in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

Properties

Article ID: 209192 - Last Review: June 28, 2004 - Revision: 2.0
APPLIES TO
  • Microsoft Access 2000 Standard Edition
Keywords: 
kbhowto kbusage KB209192

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com