ACC2000: Error Message: "One or More Operators in the Filter Expression Is Invalid" Error When You Apply a Filter in an Access Project (264254)


The information in this article applies to:

  • Microsoft Access 2000
This article was previously published under Q264254
Moderate: Requires basic macro, coding, and interoperability skills.

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

SYMPTOMS

If you are not a member of the db_owner role and you try to apply a filter (by using a filter by selection, a filter by form, or a server filter) to a datasheet or to a form in an Access project, the filter is not applied, and you may receive the following error message:
One or more operators in the filter expression is invalid.
For a valid list of operators refer to the help file.

RESOLUTION

To resolve this problem, obtain the latest Microsoft Jet 4.0 service pack that contains an updated version of the Microsoft Jet 4.0 database engine.

For additional information about how to obtain the latest Microsoft Jet 4.0 service pack, click the following article number to view the article in the Microsoft Knowledge Base:

239114 How To: Obtain the Latest Service Pack for the Microsoft Jet 4.0 Database Engine

To temporarily work around this behavior:
  • For forms, create a form, and then change the RecordSource property of the form from dbo_<table name> to just <table name> where <table name> is the name of your table.
  • For Datasheet views of a table, create a form, and then set the DefaultView property of the form to Datasheet. Then use the form instead of using the table directly.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

MORE INFORMATION

Steps to Reproduce the Behavior

  1. Create a new project to the Microsoft Data Engine (MSDE) or Microsoft SQL Server 7.0 Northwind sample database.
  2. Create a new user who is not a member of the db_owner group. To do so, follow these steps:
    1. On the Tools menu, point to Security, and then click Database Security. The SQL Server Security dialog box appears.
    2. On the Server Logins tab, click Add.
    3. In the SQL Server Login Properties dialog box, type a new user name.
    4. Click SQL Server authentication, leave the password blank, and then click Northwind in the default database drop-down list.
    5. Click the Database Access tab, and then click to select the Permit check box to assign permissions to the Northwind sample database. Leave the default Public role selected.
    6. Click OK twice to close the SQL Server security dialog box.
  3. On the File menu, click Connection.
  4. Verify that Use a specific user name and password is selected, and then type the user name that you just created. Click OK to close the dialog box. This resets the connection of the ADP to the new user.

    NOTE: When the new user is active, the table-name listing includes (dbo) after each table name.
  5. In the Database window, click Forms under Object, and then use the Form Wizard to create a new form. Open the form, and then apply a filter by selection or a filter by form. Note that you receive the error mentioned in the "Symptoms" section of this article.
  6. To resolve the error, edit the RecordSource property of the form to remove the dbo_ from the beginning of the table name.
  7. Reapply the filter and note that the error no longer occurs.
Modification Type:MinorLast Reviewed:1/26/2005
Keywords:kbbug KbClientServer kberrmsg kbnofix KB264254 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.