Registrations are now open. Join us today!
There is still a lot of work to do on the wiki yet! More information about editing can be found here.
Already have an account?

Microsoft KB Archive/106184

From BetaArchive Wiki

Article ID: 106184

Article Last Modified on 5/6/2003


  • Microsoft Access 1.0 Standard Edition
  • Microsoft Access 1.1 Standard Edition
  • Microsoft Access 2.0 Standard Edition

This article was previously published under Q106184

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


If a report that is based on a query contains a field that includes a null value from an Immediate If (IIf) statement, you will not be able to format the field properly on the report.


There are two ways to work around this behavior. The first method is to format the IIf expression in the query. You can add a format statement to the beginning of the IIf statement. For example, the following statement would add formatting to the field:

   Format(IIF([freight] = 32.07,[freight],null),"$0.00")

The second method is to use the IIf statement on the report directly, and not in the query. For example, you could add the following statement to the ControlSource property of the expression field's text box:



This behavior no longer occurs in Microsoft Access version 7.0.


Steps to Reproduce Behavior

  1. Open the sample database NWIND.MDB.
  2. Create the following new query based on the Customers and the Orders tables. Join the two tables on the Customer ID field. Save the query as TestIIF:

          Query: TestIIF
          Field: Company Name
          Field: Order ID
          Field: Freight
          Field: Shipped Date
          Field: MyField: IIf([freight]=32.07,[freight],null)
  3. Use the ReportWizards to create a new single-column report based on the TestIIF query. Include all the fields on the report.
  4. Choose Print Preview to verify that the MyField field is unformatted.
  5. Choose Cancel to return to Design view. Try to format the field.

Keywords: kbfix kbprb kbusage KB106184