ACC: "Can't Bind Name '[XXX]'" Error with Crosstab Query

• Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

SYMPTOMS

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

If you create a crosstab query using an implicit parameter [XXX] in a WHERE clause (criteria), you may receive the following error message:

   In Microsoft Access 97:

      The Microsoft Jet database engine does not recognize '[XXX]' as a
      valid field name or expression.

   In earlier versions of Microsoft Access:

      Can't bind name '[XXX]'

CAUSE

A crosstab query dynamically generates column names. Therefore, Microsoft Access cannot tell whether [XXX] is referring to a parameter or a column name until after the query is bound.

When you build a graph, Microsoft Access uses a crosstab to build the data to graph. If the query is a parameter query, you have to define the parameters explicitly, as you would have to for a normal crosstab query.

RESOLUTION

To avoid this error, define [XXX] as an explicit parameter by adding it to the Query Parameters dialog box.

STATUS

This behavior is by design.

MORE INFORMATION

Steps to Reproduce Behavior

1. Open the sample database Northwind.mdb (or NWIND.MDB in version 2.0 or earlier).

2. Create a new crosstab query based on the Order Details table.

3. Drag the UnitPrice, Discount, and OrderID fields to the QBE grid, and set the values in the grid as follows.

   NOTE: Type a space in the Unit Price and Order ID fields in version 1.x and 2.0.

         Crosstab Query: Query1
         ---------------------------------
         Field: UnitPrice
            Total: Group By
            Crosstab: Row Heading
            Criteria: > [Enter an Amount:]
         Field: Discount
            Total: Group By
            Crosstab: Column Heading
         Field: OrderID
            Total: Count
            Crosstab: Value
   
   

4. Run the query. Note that you receive the error message mentioned in the "Symptoms" section.

5. Click OK to return to Query Design view, and then click Parameters on the Query menu.

6. Enter the following under Parameters:

         [Enter an Amount:]
   

7. Select Currency as the data type, and then click OK.

8. Run the query and type any amount (for example, type "2"). Note that the query runs.

REFERENCES

For more information about crosstab queries, please see the following articles in the Microsoft Knowledge Base:

   ARTICLE-ID: Q104758
   TITLE     : ACC: Error with Linked Crosstab Subform or Subreport

   ARTICLE-ID: Q109939
   TITLE     : ACC: Creating a Crosstab Query with Multiple Value Fields