PRB: Mixing "And" and "Or" Criteria in the Same Filter Returns a 3001 Error Message (280811)


The information in this article applies to:

  • Microsoft Visual Basic Professional Edition for Windows 6.0
  • Microsoft Visual Basic Enterprise Edition for Windows 6.0
  • ActiveX Data Objects (ADO) 2.5
  • ActiveX Data Objects (ADO) 2.6
  • ActiveX Data Objects (ADO) 2.7
This article was previously published under Q280811

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.
Modification Type:MajorLast Reviewed:8/23/2001
Keywords:kbDatabase kbDSupport kbprb KB280811 kbAudDeveloper
©2002 Microsoft Corporation. All rights reserved.