Microsoft KB Archive/119755

= ACC: Query with Expression on Expression Prompts for Parameter =

Article ID: 119755

Article Last Modified on 1/19/2007

-

APPLIES TO


 * 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 Q119755



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
This behavior can occur if you create a calculated field with an expression based on another expression on the query, and this field has a sort order or criteria. Microsoft Access supports referencing column aliases only in other field cells.

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:


 * Substitute the first expression for the first expression's name in the second expression.
 * Create a second query based on the initial query and include the sort order and criteria in the second query.

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



Steps to Reproduce Behavior
 Open the sample database Northwind.mdb (or NWIND.MDB in versions 1.1 and 2.0), and create a new query based on the Order Details table. Drag the Quantity field from the field list to the query grid.  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.)  Run the query. Note that the query runs correctly. View the query in Design view again.</li> Set the Total column's sort order to Ascending.</li> Run the query again. Note that you are prompted to enter a parameter value even though no parameter is defined in the query.</li></ol>

The SQL statement for this query is:

<pre class="fixed_text">  SELECT DISTINCTROW [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 expression, which is not allowed.

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

<ul>  Change the Total expression in step 3 to be:

<pre class="fixed_text">     Total: (5*[Quantity])+20

This change substitutes the first expression for the first expression's name in the second expression. </li> Save the sample query after step 4, and then create a new query based on the sample query to include the sort order for the Total expression.</li></ul>

<div class="references_section">