ACC2002: Parameter Prompt Appears Twice When You Run a Query (287432)


The information in this article applies to:

  • Microsoft Access 2002
This article was previously published under Q287432
For a Microsoft Access 2000 version of this article, see 245467.
Moderate: Requires basic macro, coding, and interoperability skills.

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

SYMPTOMS

When you run a parameterized query, you receive two prompts for each parameter that is defined in the query.

CAUSE

Sorting has been applied to the query in Datasheet view rather than in Design view.

RESOLUTION

To correct a query in this state, clear the OrderBy property in the property sheet in Design view of the query. To clear the OrderBy property of the query, follow these steps:
  1. Open the query in Design view.
  2. Click any empty space in the top pane of the query. Do not click on a table or a field.
  3. On the View menu, click Properties.
  4. Clear the OrderBy property in the property sheet.
  5. Save the query, and then close it.
To prevent this behavior from occurring in new queries, be sure to apply sorting to the fields of a query in Design view by using the Sort row in the query design grid.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

MORE INFORMATION

There are two ways to sort the results of a query. You can sort it by selecting Ascending or Descending in the Sort row under the relevant field in Design view of the query. Or you can run the query, and then click either the Sort Ascending or the Sort Descending command on the Records menu.

If you sort the query in Design view, Microsoft Access adds the ORDER BY clause to the SQL statement of the query. If you sort the query in Datasheet view, Microsoft Access stores the sort order in the OrderBy property of the query. When the OrderBy property has been set, either on the property sheet in Design view or in Datasheet view, Microsoft Access will prompt you twice for each parameter in the query. This occurs because Microsoft Access runs the query initially to retrieve the correct records, and then runs it again to apply the sort order that you specified.

Steps to Reproduce the Behavior

  1. Open the sample database Northwind.mdb.
  2. On the View menu, point to Database Objects, and then click Queries.
  3. Click the Employee Sales by Country query, and then click Open.
  4. When you are prompted for the Beginning Date parameter, type 1/1/1997, and then click OK.
  5. When you are prompted for the Ending Date parameter, type 12/31/1997, and then click OK.
  6. Click the LastName column, and then on the Records menu, point to Sort, and click Sort Descending.
  7. Save the query, and then close it.
  8. Run the query from the Database window.
Note that you are now prompted twice for each parameter.
Modification Type:MajorLast Reviewed:11/6/2003
Keywords:kbbug kbnofix KB287432
©2003 Microsoft Corporation. All rights reserved.