ACC: Formatting Immediate IF (IIF) Statements in Reports
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 : kbusage GnlFrmt
Version : 1.0 1.1 2.0
Platform : WINDOWS
Issue type : kbprb