ACC2000: Query Saved with a Filter Does Not Limit the Recordset upon Next Use (208415)


The information in this article applies to:

  • Microsoft Access 2000
This article was previously published under Q208415
Novice: Requires knowledge of the user interface on single-user computers.

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

SYMPTOMS

When you save the results of a query after you apply additional filters using Filter By Selection or Filter By Form, the saved recordset consists of all records that were returned by the query before you applied the additional filters.

CAUSE

Any criteria that you apply when you use Filter By Selection or Filter By Form is saved in the Filter property of the query. When you open the query again, it shows all the records. The Filter property is not applied until you click the Apply Filter button on the toolbar or click Apply Filter/Sort on the Records menu.

RESOLUTION

To ensure that only the necessary records are saved with the new query, you need to manually add all additional filtering as criteria within the QBE grid or the SQL SELECT statement.

MORE INFORMATION

Any field names and values chosen when you use Filter By Selection or Filter By Form are stored with the query's Filter property. This filter affects only the output of the query in memory (when applied), and does not modify the SELECT statement. When you click Save As on the File menu, Microsoft Access saves the query's actual SELECT statement (the query's design), not the results of what the query holds in memory.

Steps to Reproduce Behavior

  1. Start Microsoft Access and open the sample database Northwind.mdb.
  2. In the Database window, click Queries under Objects, and then click New.
  3. Create a query using the Customers table and the Simple Query Wizard. Include all the fields in the query and save it as Customers Query.
  4. Run the query.

    Note the number of records being displayed on the status bar. For example, it may read "Record 1 of 91." This number will be important later.
  5. Press the TAB key until the field value in the Contact Title column is highlighted (approximately three times).

    NOTE: This field value will most likely be Sales Representative. However, the field's actual value is not important.
  6. Point to Filter on the Records menu, and then click Filter By Selection.

    NOTE: The status bar now displays a different number. For example, it may display "Record 1 of 17 (filtered)."
  7. Click Save As on the File menu.
  8. In the Save Query box, type Filter Test, and then click OK. Close the Datasheet view.
  9. In the Database window, click Queries, click Filter Test, and then click Open.

    NOTE: The datasheet's status bar displays the same number of records that were displayed in step 4. All original records are included and not filtered as expected. The criteria that you applied was saved in the Filter property.
  10. If you click the Apply Filter button, you receive the Enter Parameter Value dialog box. This is because the original query name (such as Customers Query) was saved with the Filter property. Click Cancel, and then click Design View on the View menu. Click the background of the query, and then click Properties on the View menu.
  11. The Filter property contains: (([Customers Query].Contact Title="Sales Representative")). Modify this property to read: (([Filter Test].Contact Title="Sales Representative")). Click Save on the File menu, and then click Datasheet View on the View menu.
  12. Click the Apply Filter button.

    The query should now display the same number of records that were displayed in step 6.

REFERENCES

For more information about filtering by selection or filtering by form, click Microsoft Access Help on the Help menu, type create a filter in a table, query, or form in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
Modification Type:MajorLast Reviewed:6/24/2004
Keywords:kbprb kbusage KB208415
©2004 Microsoft Corporation. All rights reserved.