Microsoft KB Archive/291054

= ACC2002: &quot;#Error&quot; Message When You Use TRIM Function in a Form or a Report =

Article ID: 291054

Article Last Modified on 11/6/2003

-

APPLIES TO


 * Microsoft Access 2002 Standard Edition

-



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, &quot;#Error&quot; 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 &quot;#Error.&quot;



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



Steps to Reproduce the Behavior
 Create a table that has two text fields named Field1 and Field2. Create a report that is 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 that is 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">