Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 824160 - Last Review: June 8, 2004 - Revision: 2.3
You receive incorrect results when you apply a filter in a Microsoft Access project
This article applies only to a Microsoft Access project (.adp).
Moderate: Requires basic macro, coding, and interoperability
skills.
On This Page
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:
Collapse this tableExpand this table
| Filter | Run at | Filter Syntax |
|---|
| Filter Property | Client | Microsoft Jet |
| Filter by Form | Client | Microsoft Jet |
| ServerFilter Property | Server | SQL |
| ServerFilterByForm Property | Server | SQL |
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'
- On the Filter menu, click Apply
Filter.
- 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.
APPLIES TO
- Microsoft Office Access 2003
- Microsoft Access 2002 Standard Edition
| kbadp kbdesign kbprogramming kbdatabase kbfilters kbprb KB824160 |
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate
Be the first to leave feedback, to help others about this knowledge base
article.
(Optional) Name
(Optional)
Public URL Or Email
Comments
No
HTML -- Text Only Please