ACC2000: When You Sort Parameter Query Results You Are Prompted for Parameters Again

ID: Q245467

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

In Access 2000, each time that you sort a query that has parameters, the query prompts you for the parameters again. This behavior does not occur in Access 97.

RESOLUTION

You can create a form to hold the parameters, and then have the query refer to that form. To see how this works, follow these steps:

Open the sample database Northwind.mdb.

In the Database window, click Queries, and then click New.

In the New Query dialog box, click Design, and then click OK.

In the Show Table dialog box, click the Orders table, and then click OK.

Add the following fields to the query design grid:


   Field: OrderID
   Table: Orders

   Field: CustomerID
   Table: Orders

   Field: OrderDate
   Table: Orders
   Criteria: Is Not Null And Between [Forms]![frmOrdersByDate]![StartDate]
             And [Forms]![frmOrdersByDate]![EndDate]

On the Query menu, click Parameters.

Type the following parameters, and select the following data types:

Parameter Data type

[Forms]![frmOrdersByDate]![StartDate] Date/Time

[Forms]![frmOrdersByDate]![EndDate] Date/Time

Save the query as OrdersByDate, and then close it.

Create the following new form:


   Form: frmOrdersByDate
   ---------------------
   Caption: TestForm

   Command button
   ---------------------
   Name: Button0
   Caption: Show Records

   TextBox
   ---------------
   Name: StartDate

   TextBox
   ------------------------------
   Name: EndDate

Set the OnClick property of the command button to the following event procedure:


Private Sub OK_Click()
   DoCmd.OpenQuery "OrdersByDate"
End Sub

Save the form as frmOrdersByDate, and then open the form in Form view.

For the parameters, enter the following:


   StartDate: 1/1/1997
   EndDate:   12/31/1997

Click the Show Records button. The query results are displayed.

On the Records menu, point to Sort, and then click Sort Ascending.

Note that the data is sorted, and that you are not prompted for the parameters again.

STATUS

MORE INFORMATION

Steps to Reproduce Behavior

Open the sample database Northwind.mdb.

Run the "Sales by Year" query.

For the parameters, enter the following:
```
   StartDate: 1/1/1997
   EndDate:   12/31/1997 
```
The query results are displayed.

On the Records menu, point to Sort, and then click Sort Ascending.

Note that you are prompted for the parameters again.

Additional query words: prb

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