You receive incorrect results when you apply a filter in a Microsoft Access project

When you apply a filter in a Microsoft Access project to display a subset of records, you may receive a result set that does not match the filter condition.

To work around this problem, you must use the appropriate syntax in filter expressions. This syntax may be Microsoft Jet syntax or SQL syntax, depending on where the filter expression is run.

You may work around this problem by using the Microsoft Jet syntax in the filter expression because the filter expression is run on the client side.

For example, change the filter expression fromto

This behavior is by design.

In Microsoft Access, you may apply different types of filters to a form, to a report, to a query, or to a table table to display a subset of records. Depending on the type of filter that you use, the filter expression is run on the client side or on the server side. Therefore, the syntax that you must use in the filter expression varies with the filter that you use.

When you specify a filter that runs on the client side, you must use the Microsoft Jet syntax. For a filter that runs on the server side, you must use SQL syntax. The following table lists the different types of filters, where the filter expression runs, and the syntax that you must use:

Steps to Reproduce the Behavior

1. Start Microsoft Access.
2. Open the NorthwindCS.adp sample project.
3. In the Project window, click Forms under Objects.
4. In the right pane, double-click Orders.
5. In the Project window, on the Records menu, point to Filter, and then click Filter By Form.
6. In the Order Date box, type the following:
   Between '4/1/1996' and '11/1/1996'
7. On the Filter menu, click Apply Filter.
8. On the View menu, click Datasheet View.
   
   Notice that in the list of results that is displayed in the Datasheet view, the OrderDate column contains a number of dates. Each of these dates falls between November 1996 and November 1998.