Microsoft KB Archive/202025

= ACC2000: "#Error" Message Using TRIM Function in Form/Report =

Article ID: 202025

Article Last Modified on 6/24/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q202025



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

For a Microsoft Access 2002 version of this article, see 291054.



SYMPTOMS
If a report or a form has its RecordSource property defined as a table that 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 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 presents the result for the control. If Access evaluates a blank field, it displays "#Error."



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



Steps to Reproduce Behavior
 Create a table with two text fields named Field1 and Field2. Create a report based on this table. Add the Field1 and Field2 fields to the detail section of the report. Modify the ControlSource property for the Field1 field as follows:

=Trim([Field1])

 Preview the report. Note that the Field1 field contains #Error and that the Field2 field is blank.</li> Create a form based on the table.</li> Add the Field1 and Field2 fields to the form.</li> Repeat step 4.</li> View the form in Form view. Note that the Field1 field contains #Error and that the Field2 field is blank.</li></ol>

<div class="references_section">