XL2000: 'Invalid Field Name' Error Message Appears When You Add a Field to Microsoft Query (211682)


The information in this article applies to:

  • Microsoft Excel 2000
  • Microsoft Query
This article was previously published under Q211682

SYMPTOMS

In Microsoft Query 2000 for Windows, if you attempt to add a field to the data pane by double-clicking a field in the list of fields, you may receive the following error message:
Microsoft Query
" is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long.
When you click OK, all data and criteria may disappear from Microsoft Query.

CAUSE

This problem can occur when the field name in the list of fields includes a period. For example, double-clicking the following field names may cause the problem to occur:

Customers.CompanyName

Shippers.CompanyName

WORKAROUND

To work around this problem, use the Query Wizard to add such fields to the data pane. To do this, when you create a new query, follow these steps:
  1. In Microsoft Query 2000, click New on the File menu.
  2. In the Choose Data Source dialog box, click the data source that you want to use. Make sure that the Use the Query Wizard to create/edit queries check box is selected, and then click OK.
  3. In the Query Wizard - Choose Columns dialog box, click the tables and columns that you want to include in the query, and then add them to the query by clicking the > button. When you are finished, click Next.
  4. In the Query Wizard - Filter Data dialog box, apply any filters that you want to use, and then click Next.
  5. In the Query Wizard - Sort Order dialog box, apply any sort order that you want, and then click Finish.

    NOTE: If you are returning external data into Microsoft Excel 2000, click Next, click View data or edit query in Microsoft Query, and then click Finish.
It is not possible to add a field with a name that contains a period to an existing query, even if you manually modify the Structured Query Language (SQL) statement used to create the query.

STATUS

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

MORE INFORMATION

This problem occurs because of the way in which Microsoft Query 2000 parses the SQL statement that is used to create a query. Normally, the reference to a table and field in a SQL statement resembles the following example:

<TableName>.<FieldName>
Invoices.Address
Invoices.City

However, if the field name contains a period, the reference in the SQL statement resembles the following example:

<TableName>.<FieldName>.<SecondPartOfFieldName>
Invoices.Customers.CompanyName
Invoices.Shippers.CompanyName

Microsoft Query 2000 can handle a field name that contains a period only if you add the field to the data pane by using the Query Wizard.

NOTE: It is rare for a field name to contain a period. However, the Northwind database included with Microsoft Office 2000 includes a table that contains two fields with names that include periods.
Modification Type:MajorLast Reviewed:10/8/2003
Keywords:kbbug kbpending KB211682
©2003 Microsoft Corporation. All rights reserved.