Microsoft KB Archive/824160

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

Article ID: 824160

Article Last Modified on 6/8/2004

-

APPLIES TO


 * Microsoft Office Access 2003
 * Microsoft Access 2002 Standard Edition

-



This article applies only to a Microsoft Access project (.adp).

Moderate: Requires basic macro, coding, and interoperability skills.



SYMPTOMS
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.



WORKAROUND
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 from Between '4/1/1996' and '11/1/1996' to Between #4/1/1996# And #11/1/1996#



STATUS
This behavior is by design.



MORE INFORMATION
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
 Start Microsoft Access. Open the NorthwindCS.adp sample project. In the Project window, click Forms under Objects. In the right pane, double-click Orders. In the Project window, on the Records menu, point to Filter, and then click Filter By Form. In the Order Date box, type the following:

Between '4/1/1996' and '11/1/1996'

</li> On the Filter menu, click Apply Filter.</li> 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.</li></ol>

Additional query words: Access 2003 Acc2002 wrong invalid results

Keywords: kbadp kbdesign kbprogramming kbdatabase kbfilters kbprb KB824160

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.