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


The information in this article applies to:

  • Microsoft Office Access 2003
  • Microsoft Access 2002
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:
FilterRun atFilter Syntax
Filter PropertyClientMicrosoft Jet
Filter by FormClientMicrosoft Jet
ServerFilter PropertyServerSQL
ServerFilterByForm PropertyServerSQL

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.
Modification Type:MinorLast Reviewed:6/8/2004
Keywords:kbADP kbdesign kbProgramming kbDatabase kbfilters kbprb KB824160 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.