ACC2002: Name AutoCorrect Does Not Fix Changed Field Names in Union Query (282322)

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

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

SYMPTOMS

In Microsoft Access, when you attempt to run a union query, you may receive an error message similar to the following:

The ORDER BY expression (MyFieldName) includes fields that are not selected by the query. Only those fields requested in the first query can be included in an ORDER BY expression.

CAUSE

This behavior can occur if you have changed the name of one or more of the fields in a table used by the union query.

RESOLUTION

To resolve this issue, edit the union query and replace the old instance of the field name with the new field name.

STATUS

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

MORE INFORMATION

Steps to Reproduce the Problem

CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

Open the sample database Northwind.mdb.
Open the Customers table in Design view.
Replace the "CompanyName" field name with CoName. Close and save the table.
Open the Customers and Suppliers by City query.

Note that you receive the error message described in the "Symptoms" section.

REFERENCES

For more information about how to troubleshoot Name AutoCorrect, click Microsoft Access Help on the Help menu, type troubleshoot name autocorrect in the Office Assistant or the Answer Wizard, and then click Search to view the topic.