ACC2000: Error When Running Crosstab Query with a Parameter

Article translations Article translations
Article ID: 209778 - View products that this article applies to.
This article was previously published under Q209778
Moderate: Requires basic macro, coding, and interoperability skills.

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

Expand all | Collapse all

On This Page

SYMPTOMS

If you create a crosstab query that uses an implicit parameter [XXX] or a form reference in a WHERE clause (criteria), you may receive the following error message:
The Microsoft Jet database engine does not recognize '[XXX]' as a valid field name or expression.

CAUSE

A crosstab query dynamically generates column names. Therefore, Microsoft Access cannot tell whether [XXX] or a form reference is referring 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

To avoid this error, define [XXX] as an explicit parameter by adding it to the Query Parameters dialog box. To do so, follow these steps:
  1. Start Microsoft Access.
  2. In the Database window, click Queries under Objects.
  3. Open the query in Design view.
  4. To explicitly define the parameters, click Parameters on the Query menu.
  5. Type the name of the parameter or the form reference under Parameter in the Query Parameters dialog box, and then set the appropriate data type.
  6. Close the Query Parameters dialog box.The query now works as expected.

MORE INFORMATION

Steps to Reproduce the Behavior

  1. Open the sample database Northwind.mdb.
  2. Create a new crosstab query that is based on the Order Details table.
  3. Drag the UnitPrice, Discount, and OrderID fields to the query design grid, and then set the values in the query design grid as follows:
       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). Note 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:
209218 ACC2000: Error with Linked Crosstab Subform or Subreport
209143 ACC2000: Creating a Crosstab Query with Multiple Value Fields
For more information about parameter queries, click Microsoft Access Help on the Help menu, type troubleshoot parameter queries in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

Properties

Article ID: 209778 - Last Review: June 24, 2004 - Revision: 2.0
APPLIES TO
  • Microsoft Access 2000 Standard Edition
Keywords: 
kberrmsg kbprb KB209778

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com