ACC: Formatting Immediate IF (IIF) Statements in Reports

Last reviewed: May 28, 1997
Article ID: Q106184

The information in this article applies to:

Microsoft Access versions 1.0, 1.1, 2.0

SYMPTOMS

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.

RESOLUTION

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:

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

STATUS

This behavior no longer occurs in Microsoft Access version 7.0.

MORE INFORMATION

Steps to Reproduce Behavior

Open the sample database NWIND.MDB.

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)

Use the ReportWizards to create a new single-column report based on the TestIIF query. Include all the fields on the report.

Choose Print Preview to verify that the MyField field is unformatted.

Choose Cancel to return to Design view. Try to format the field.

Keywords : GnlFrmt kbusage
Version : 1.0 1.1 2.0
Platform : WINDOWS
Hardware : X86
Issue type : kbprb
Resolution Type : kbfix

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 28, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.