MS Access: Trim function will not work in a report in Access 2003/XP/2000/97
Question: In Microsoft Access 2003/XP/2000/97, I've created a report and I'm trying to use the trim function in a field on the report. But when I try to, the field shows up as #Error.
How can I get the trim function to work in a report?
Answer: To use the trim function in Access, there are 2 steps that you must perform.
Let's look at a report to demonstrate.
In the picture below, we've tried to use the trim function on the Category Name field and it is generating a #Error for each line.
To fix this, open your report in design view and select the properties for the Category Name field (ie: the field that you are trying to apply the trim function to)
Step 1 is to set the Control Source property to:
Step 2 is to change the name of the text box (from the original name). If you leave the name as the original, Access will generate an error.
In our example, we've changed the Name property to:
Now when we run the report, the Category Name shows up properly. See below.