Labels that are converted from an Access 97 database to an Access 2002 database display "#Error" in the text boxes that are bound to Report!Name (282321)


The information in this article applies to:

  • Microsoft Access 2002
  • Microsoft Access 97
This article was previously published under Q282321
Novice: Requires knowledge of the user interface on single-user computers.

This article applies only to a Microsoft Access database (.mdb).

SYMPTOMS

When you open a Microsoft Access 97 database in Microsoft Access 2002 and you convert the Access 97 database to the current file format, a problem occurs. Label reports do not correctly display some data. Data from fields that are named Name, appear as "#Error" in the converted database. Data from fields that are named Name were presented correctly in the Access 97 labels.

CAUSE

This problem occurs because the text box is bound to a field. The name of the field is the same name as an Access reserved name or a global keyword.

RESOLUTION

To work around this problem, use one of the following methods.

Set the ControlSource Property for the Text Box to Name

To set the ControlSource property for the text box to Name, follow these steps:
  1. Open the report in Design view.
  2. Select the control that displays #Error.
  3. If the Properties window is not showing, click Properties on the View menu.
  4. Select the Data tab.
  5. Change the Control Source from =Trim(Report![Name]) to Name.
Use a query to create an alias for the field called Name.
  1. In the Database window, under Objects, click Queries then double-click Create query in Design view.
  2. In the Show Table dialog box, select the table you want to create the report from, click Add, and then click Close.
  3. Double-click the title bar of the Field List to select all fields, and then drag all fields to the design grid.
  4. Change the entry in the Field row in the Name column to FullName:Name.
  5. Save the query as qryCustAlias and close it.
  6. Use the Label Wizard to create labels based on this query rather than on the table.
Include on the label a hidden text box for which the ControlSource property is simply Name.
  1. In the Database window, under Objects, click Reports and then click your report.
  2. Click Design to open the report in Design view.
  3. If the Field List is not visible, click Field List on the View Menu.
  4. Drag the Name field from the Field List to the Detail section of the report, and if the property sheet is not visible, on the View menu click Properties.
  5. On the Format tab of the property sheet, set the Visible property to False.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

MORE INFORMATION

When you use the Label Wizard in Access 97 to create labels using data from a field called Name, the labels will include a text box for which the ControlSource property is =Trim(Report!Name). When the database is converted to the Access 2002 structure and you preview the labels, the text box displays "#Error". If the expression is changed to =Trim(Name), the name of the report is displayed. If the ControlSource property is set to simply Name, the bound data is displayed correctly.

When you are designing tables, you should avoid using field names that are the same as the names of properties or functions that already exist in Access; examples of field names to avoid include Name and Date. Access includes a feature called Name AutoCorrect that corrects common side effects when you change the names of fields in tables, but Name AutoCorrect does not correct references to fields when they are found in functions such as Trim(). For more information about Name AutoCorrect, click Microsoft Access Help on the Help menu, type autocorrect in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

Steps to Reproduce Behavior

  1. Start Access 97 and create a new Blank Database with the name LabelNames.
  2. On the File menu, point to Get External Data, then click Import and browse to select the sample database Northwind.mdb.
  3. On the Tables tab, click Customers, then click OK.
  4. Open the Customers table in Design view, change the field name ContactName to Name, and save and close the table.
  5. On the Reports tab, click New.
  6. In the New Report dialog box, click Label Wizard and select Customers from the Choose the table ... list.
  7. In the Label Wizard, click Next twice, double-click Name in the Available Fields list, and then press Enter.
  8. Double-click ContactTitle in the Available Fields list, and then click Finish.

    Notice that the contact person's name and title appear correctly.
  9. Open the label report in Design view and notice that the ControlSource property for the first text box is =Trim(Report![Name])
  10. Close the label report and close the database.
  11. Start Access 2002 and open the database LabelNames.mdb.
  12. Select the option Convert Database, click OK and save the converted database with the name LabelNamesNew.
  13. Click OK to the message about sharing with Access 97 users.
  14. Under Objects, select Reports, click Labels Customers, and then click Preview.

    Notice that while the contact person's title appears correctly, the text box for the person's name shows "#Error".

REFERENCES

For additional information about reserved names, click the article number below to view the article in the Microsoft Knowledge Base:

286335 ACC2002: Reserved Words in Microsoft Access

Modification Type:MajorLast Reviewed:11/19/2004
Keywords:kbbug kbnofix KB282321
©2004 Microsoft Corporation. All rights reserved.