Query97: "Invalid Bracketing" Error Adding Field to MS Query

ID: Q173043

The information in this article applies to:

Microsoft Excel 97 for Windows
Microsoft Query 97 for Windows

SYMPTOMS

In Microsoft Query 97 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
   Invalid bracketing of name '<TableName>.<FieldName>'.

When you click OK, all data and criteria may disappear from Microsoft Query.

CAUSE

This problem may 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 97, click New on the File menu.

2. In the Choose Data Source dialog box, click the data source you want

   to use. Make sure that the "Use the Query Wizard to create/edit
   queries" check box is checked. 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 add them to
   the query by clicking the ">" button. When you are done, click Next.

4. In the "Query Wizard - Filter Data" dialog box, apply any filters you

   want to use. Then, click Next.

5. In the "Query Wizard - Sort Order" dialog box, apply any sort order you

   want. Then, click Finish.

   NOTE: If you are returning external data into Microsoft Excel 97,
   click Next. Then, click "View data or edit query in Microsoft Query,"
   and 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 this to be a problem in the Microsoft products listed at the beginning of this article.

MORE INFORMATION

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

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

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

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

Microsoft Query 97 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 that it is rare for a field name to contain a period. However, the Northwind database included with Microsoft Office 97 includes a table that contains two fields with names that include periods.

Additional query words: XL97 invoices access

Keywords          : kbtool xlquery 
Version           : WINDOWS:97
Platform          : WINDOWS
Issue type        : kbbug

Last Reviewed: November 2, 1998