ACC2002: "#Error" Message When You Use TRIM() Function in a Form or a Report (291054)


The information in this article applies to:

  • Microsoft Access 2002
This article was previously published under Q291054
Moderate: Requires basic macro, coding, and interoperability skills.

For a Microsoft Access 2000 version of this article, see 202025.

SYMPTOMS

If a report or a form has its RecordSource property defined as a table, and if the table contains no records, "#Error" is displayed when you preview the report or view the form in Form view if a bound control on the report or the form is defined by using the Trim() function.

CAUSE

When you use an expression to define the ControlSource property for a form or report, Microsoft Access evaluates the expression, and then presents the result for the control. If Access evaluates a blank field, Access displays "#Error."

RESOLUTION

To avoid this error, surround the RecordSource property with an IIF (Immediate If) statement.

MORE INFORMATION

Steps to Reproduce the Behavior

  1. Create a table that has two text fields named Field1 and Field2.
  2. Create a report that is based on this table.
  3. Add the Field1 and Field2 fields to the detail section of the report.
  4. Modify the ControlSource property for the Field1 field as follows:

    =Trim([Field1])

  5. Preview the report. Note that the Field1 field contains #Error and that the Field2 field is blank.
  6. Create a form that is based on the table.
  7. Add the Field1 and Field2 fields to the form.
  8. Repeat step 4.
  9. View the form in Form view. Note that the Field1 field contains #Error and that the Field2 field is blank.

REFERENCES

For more information about the Trim() Function, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type trim in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
Modification Type:MajorLast Reviewed:11/6/2003
Keywords:kbdta kberrmsg kbprb kbusage KB291054
©2003 Microsoft Corporation. All rights reserved.