Fix Microsoft Access Mailing Labels Showing Error for Names and Cities
Question details
User needs to resolve an issue where Avery 8160 mailing labels in Microsoft Access display '#Error' for name and city/state fields, despite street addresses printing correctly.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Generating and printing Avery 8160 mailing labels using a Microsoft Access report.
- Observed behavior
- Street addresses print correctly, but the names and city or state fields display as Error. Additionally, an unexpected parameter prompt referencing TRIM may appear.
Before modifying the report layout, open the underlying query used by the label report in Datasheet View to confirm that all required address fields are present and returning data without errors.
Fix Control Name and Control Source Conflicts
Use this solution if your text box control shares the exact same name as a field used in its calculated expression, which commonly causes the #Error display.
In Microsoft Access, if a text box's Control Source begins with an equals sign (indicating a calculation or concatenation, such as =[FirstName] & " " & [LastName]), the Name property of that text box cannot match the name of any field used in the expression.
Right-click your mailing label report in the Navigation Pane and select 'Design View'.
Click on the text box control that is displaying the Error for names or cities.
Press F4 to open the Property Sheet. Navigate to the 'All' tab to view the control's properties.
Locate the 'Name' property at the top. If it exactly matches a field name (e.g., 'FirstName'), change it to something unique like 'txtFirstName'.
Save the report and switch to 'Print Preview' to verify that the Error has been resolved.
Verify Missing Fields in the Label Query
Apply this fix if the report is prompting you for a parameter (like TRIM) or if the required fields are entirely missing from the report's record source.
Combine Address Fields Using Concatenation
Use a single concatenated text box to handle the entire address block, which can help prevent blank line errors and simplify control management.
Looking for a Free and Lightweight Office Suite?
While Microsoft Access handles complex database management, WPS Office is an exceptional free alternative for your daily document, spreadsheet, and presentation needs. Enjoy a seamless, lightweight experience with full format compatibility.
- 1. Download the Installer: Visit the official WPS Office website and click the free download button for your operating system.
- 2. Install WPS Office: Run the downloaded installation file and follow the on-screen prompts to complete the setup in minutes.
- 3. Open and Create: Launch WPS Office and immediately start creating, editing, and saving your documents with full Microsoft format compatibility.
Frequently Asked Questions
Why does a TRIM parameter prompt appear when printing Access labels?
A parameter prompt for TRIM usually appears if a calculated field in your report or query is trying to use the Trim() function on a field name that does not exist, is misspelled, or is missing from the report's underlying record source.
How do I fix the #Error in Access reports when using calculated fields?
The most common fix is to change the 'Name' property of the text box. If your calculation is =[City] & ", " & [State], the text box cannot be named 'City' or 'State'. Rename it to 'txtCityState' to resolve the error.
Can I suppress blank lines in Microsoft Access mailing labels?
Yes. You can use the Access 'Can Shrink' property set to Yes for the text boxes, or you can use an expression with the '+' operator instead of '&' to propagate null values, which automatically hides empty address lines.
