Microsoft KB Archive/91710: Difference between revisions

From BetaArchive Wiki
(importing KB archive)
 
m (Text replacement - ">" to ">")
 
Line 127: Line 127:
         Total: Group By
         Total: Group By
         Crosstab: Row Heading
         Crosstab: Row Heading
         Criteria: > [Enter an Amount:]
         Criteria: > [Enter an Amount:]
       Field: Discount
       Field: Discount
         Total: Group By
         Total: Group By

Latest revision as of 09:25, 21 July 2020

Article ID: 91710

Article Last Modified on 1/18/2007



APPLIES TO

  • Microsoft Access 1.0 Standard Edition
  • Microsoft Access 1.1 Standard Edition
  • Microsoft Access 2.0 Standard Edition
  • Microsoft Access 95 Standard Edition
  • Microsoft Access 97 Standard Edition



This article was previously published under Q91710

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


SYMPTOMS

If you create a crosstab query that uses 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, you receive the following error message:

Can't bind name '[XXX]'

If you use an explicit parameter, the query works as expected.

CAUSE

A crosstab query dynamically generates column names. Therefore, Microsoft Access cannot tell whether [XXX] refers to a parameter or to 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 typical crosstab query.

RESOLUTION

Method 1

Define an Explicit Parameter:

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

Method 2

Use Code to Change the Column Headings:

You can use Microsoft Visual Basic for Applications to dynamically change the column headings in the crosstab query.

For additional information about how to use code to change column headings, click the article number below to view the article in the Microsoft Knowledge Base:

155489 ACC: How to Use Code to Change Column Headings in Crosstab Query


STATUS

This behavior is by design.

MORE INFORMATION

Steps to Reproduce the Behavior

  1. Open the sample database Northwind.mdb (or NWIND.MDB in version 2.0 or earlier).
  2. Create a new crosstab query that is based on the Order Details table.
  3. Drag the UnitPrice, the Discount, and the OrderID fields to the QBE grid and then set the values in the grid as follows:

    NOTE: Type a space in the Unit Price and the Order ID fields in version 1.0, 1.1, 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 that is 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). Notice that the query runs.


REFERENCES

For additional information about crosstab queries, click the article numbers below to view the articles in the Microsoft Knowledge Base:

104758 ACC: Error with Linked Crosstab Subform or Subreport


109939 ACC: Creating a Crosstab Query with Multiple Value Fields


For more information about parameter queries, type parameter queries in the Office Assistant, click Search, and then click to view Troubleshoot parameter queries.


Additional query words: can t bind name [XXX] prb

Keywords: kberrmsg kbprb KB91710