KnowledgeBase Archive

An Archive of Early Microsoft KnowledgeBase Articles

View on GitHub

Q280811: PRB: Mixing And/Or Criteria in Same Filter Returns 3001 ErrMsg

Article: Q280811
Product(s): Microsoft Visual Basic for Windows
Version(s): 2.5,2.6,6.0
Operating System(s): 
Keyword(s): kbDatabase kbGrpDSVBDB kbDSupport kbATM kbado270
Last Modified: 23-AUG-2001

-------------------------------------------------------------------------------
The information in this article applies to:

- Microsoft Visual Basic Professional Edition for Windows, version 6.0 
- Microsoft Visual Basic Enterprise Edition for Windows, version 6.0 
- ActiveX Data Objects (ADO), versions 2.5, 2.6, 2.7 
-------------------------------------------------------------------------------

SYMPTOMS
========

Mixing And and Or criteria in the same filter returns the following error
message:

  Runtime Error '3001' Arguments are of the wrong type, are out of acceptable
  range or are in conflict with one another.

CAUSE
=====

Although there is no precedence when evaluating criteria, when you use And and
Or together in the Filter property, the or criteria must be the last comparison
made.

RESOLUTION
==========

To resolve the error message, use criteria that allows the Or to be the top
comparison.

STATUS
======

This behavior is by design.

MORE INFORMATION
================

Steps to Reproduce Behavior
---------------------------

1. Start a new Microsoft Visual Basic Standard EXE project. Form1 is created by
  default.

2. On the Project menu, click References, and then add a Reference to the
  Microsoft ActiveX Data Objects 2.X Library.

3. Insert the following code into the General Declaration section of Form1:

  Option Explicit
  Dim cn As ADODB.Connection
  Dim rs As ADODB.Recordset

  Private Sub Form_Load()
  Set cn = New ADODB.Connection
  Set rs = New ADODB.Recordset

  cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Password=;Data Source=path to nwind2000.mdb;User ID=Admin;Password=;" 
  rs.Open "Select * from employees", cn, adOpenDynamic, adLockOptimistic
  rs.Filter = "((lastname like 'd%')OR (lastname like 'l%')) AND (title='sales representative')"
  'rs.Filter = "(lastname like 'd%' AND title = 'sales representative') OR (lastname like 'l%' AND title = 'sales representative')"

  rs.MoveFirst
  Do While Not rs.EOF
      Debug.Print rs.Fields(0).Value
      rs.MoveNext
  Loop
  End Sub

4. Press the F5 key to run the project, and note that error message 3001 is
  returned.

5. Uncomment the second rs.filter line and comment the first. This time, when
  the project is run, note that three records are returned to the Immediate
  window.

Additional query words:

======================================================================
Keywords          : kbDatabase kbGrpDSVBDB kbDSupport kbATM kbado270 
Technology        : kbVBSearch kbAudDeveloper kbADOsearch kbADO250 kbADO260 kbZNotKeyword6 kbZNotKeyword2 kbVB600Search kbVB600 kbADO270
Version           : :2.5,2.6,6.0
Issue type        : kbprb

=============================================================================

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.