ACC2000: Error When Running Crosstab Query with a Parameter

ID: Q209778

The information in this article applies to:

Microsoft Access 2000

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

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

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:

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

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] 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 typical crosstab query.

RESOLUTION

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

MORE INFORMATION

Steps to Reproduce Behavior

Open the sample database Northwind.mdb.

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

Drag the UnitPrice, Discount, and OrderID fields to the QBE grid, and set the values in the 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

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

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

Enter the following under Parameters:


   [Enter an Amount:]

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

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

REFERENCES

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

Q209218 ACC2000: Error with Linked Crosstab Subform or Subreport

Q209143 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.

Additional query words: prb

Keywords : kberrmsg kbdta QryCross
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbprb