My form or report does not display all of the data that i expect to see

Symptoms

When you create a form or report and apply a filter before saving it for the first time, and then save it while the filter is still in effect, the filter cannot then be turned off. The form or report will always show the filtered view of the underlying table or query.

Cause

There is a feature designed to allow you to easily create permanently filtered forms and reports. Access does this by incorporating a filter into the object's design, if that filter is in effect when the object is first saved.

Resolution

If you do not want a filter that you have applied to a newly-created form or report to become permanent, remove the filter before you save the object for the first time.

If you have created a form or report and saved it for the first time while it is in a filtered state, and later decide that you do not want the filter to be permanent, you can edit the object's Record Source property by using the following procedure.

Remove a permanent filter from a form or report

1. In the Navigation Pane, right-click the form or report, and then click Design View on the shortcut menu. The object opens in Design view.

2. If the property sheet is not already visible, press F4 to display it.

3. Click in the object's Record Source property box, and then click . The query designer appears, with the object's recordset query displayed.

4. Look in the query design grid for the filtered fields and filter criteria. In the following figure, there is one filtered field, CompanyName, and a single criterion, Contoso, Ltd.

   

5. Select a column that contains a filtered field by clicking the gray bar just above the Field row in the query design grid.

6. Press DELETE to remove the filtered field and its criterion.

7. Repeat steps 5 and 6 for each filtered field that you want to remove from the object's design.

8. Close the query designer. When you are prompted to save the changes made to the SQL statement and update the property, click Yes.

   You have removed the permanent filter from your form or report.

   Note: Removing a permanent filter from a form or report in this manner does not preclude you from using the same filter in a normal fashion on this object in the future. It only removes it from the object's design so that you can view all the records in its underlying table or query by default.