ACC2000: Query with Expression on Expression Prompts for Parameter

ID: Q208968


The information in this article applies to:

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

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


SYMPTOMS

When you run a query, Microsoft Access prompts you to enter a parameter value even though you have not defined a parameter in the query.


CAUSE

You have an expression in your query that has a critera or sort order, and the query references a different field in the query that uses an alias.

This can be seen most easily in the query's SQL view. The SELECT list can include expression names, but other clauses, such as WHERE and ORDER BY, cannot.


RESOLUTION

There are two ways to work around this behavior:

Please see the "Steps to Reproduce Behavior" section of this article for examples of these two methods.


STATUS

Microsoft Access supports referencing column aliases only in the field list (SELECT clause) of a query, not in the ORDER BY or the WHERE clause.


MORE INFORMATION

Steps to Reproduce Behavior

  1. Open the sample database Northwind.mdb, and create a new query based on the Order Details table.


  2. Drag the Quantity field from the field list to the query grid.


  3. Add the following expressions to the query:
    
       Field: Calc: 5*[Quantity]
       Field: Total: [Calc]+20 
    Note that the second expression is based on the first expression.


  4. Run the query. Note that the query runs correctly.


  5. View the query in Design view again.


  6. Set the sort order of the Total column to Ascending.


  7. Run the query again. Note that you are prompted to enter a parameter value for "Calc," even though no parameter is defined in the query.


The SQL statement for this query is:

   SELECT [Order Details].Quantity, 5*[Quantity] AS Calc,
   [Calc]+20 AS Total
   FROM [Order Details]
   ORDER BY [Calc]+20; 
Note that the ORDER BY clause includes the Calc alias, which is not supported.

To correct the sample query, use either of the following methods:


REFERENCES

For more information about calculated fields in queries, click Microsoft Access Help on the Help menu, type "perform calculations in a query" in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

Additional query words: prb


Keywords          : kbdta QryParm 
Version           : WINDOWS:2000
Platform          : WINDOWS 
Issue type        : kbprb 

Last Reviewed: May 13, 1999