PRB: Error 3001 Comparing Fields as Criteria to Filter Recordset

Exclusão de Responsabilidade para Conteúdo BDC Extinto

Este artigo foi escrito sobre produtos para os quais a Microsoft já não fornece suporte. Por conseguinte, este artigo é oferecido "tal como está" e deixará de ser atualizado.


Comparing two fields in the criteria string of the ADO Filter property (for example: The criteria string is made up of clauses in the form: " FieldName Operator FieldName ") generates the following error:
Runtime error '3001':
The application is using arguments that are of the wrong type, are out of acceptable range, or are in conflict with one another.


This functionality cannot be accomplished with current versions of ADO. The criteria syntax of the ADO Filter property is defined as follows:
" FieldName Operator 'Literal_Value' "
The 'Literal_Value' data type could be string, number, or date. You cannot use the preceding expression to compare two fields. Attempting to do so raises error 3001.


One workaround is to have several expressions joined by Boolean operators as the Filter criteria. You can only use this if the the values of compared fields are known to the developer. This way the developer can come up with a formula for the criteria string that meets the required results. For example:
rs.Filter = "Field1 Operator1 'Value1' AND Field2 Operator2 'Value2'"

If the developer does not know the values for Field1 and Field2, then the developer has to use either a custom Filter routine (within the client application) or a stored procedure (on the server side) to accomplish the functionality dynamically. You can use control-flow statements within the custom Filter routine (or the stored procedure) to perform the necessary Field comparisons and generate the expected results.

This article provides a demonstration for such a routine in the "MORE INFORMATION" section of this article.

WARNING: The workaround, provided in the MORE INFORMATION section, is only given for illustration purpose, without any warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose.


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

More Information

The ADO Help documentation states the following for the Filter method's criteria expression -- under Remarks:

The criteria string is made up of clauses in the form FieldName - Operator - Value (for example, "LastName = 'Smith'"). You can create compound clauses by concatenating individual clauses with AND (for example, "LastName = 'Smith' AND FirstName = 'John'") or OR (for example, "LastName = 'Smith' OR LastName = 'Jones'").

It also states the following guidelines for the Criteria Value:

Value is the value with which you will compare the field values (for example, 'Smith', #8/24/95#, 12.345 or $50.00). Use single quotes with strings and pound signs (#) with dates. For numbers, you can use decimal points, dollar signs, and scientific notation. If Operator is LIKE, Value can use wildcards. Only the asterisk (*) and percent sign (%) wild cards are allowed, and they must be the last character in the string. Value cannot be Null.
Steps to Reproduce Behavior

  1. Start a new Microsoft Visual Basic project. Form1 is created by default.
  2. Set a Project Reference to the Microsoft ActiveX Data Objects 2.x Library.
  3. Insert a command button on the form. Command1 is created by default.
  4. Insert the following code into the General Declaration's section of Form1:
   Option Explicit
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset

Private Sub Command1_Click()
cn.Open "provider=SQLOLEDB;Data Source=<your data source>;User ID=<uid>;" _
"password=;Initial Catalog=pubs;"
On Error Resume Next
cn.Execute "drop table tblFilterTest"
On Error GoTo errh
' Create the test table
cn.Execute "create table tblFilterTest(ID int primary key, Field1
varchar(20), Field2 varchar(20))"
' Open ADO recordset
rs.Open "select * from tblFilterTest", cn, adOpenKeyset,
' Add first record
rs("ID") = 1
rs("Field1") = "A"
rs("Field2") = "B"

' Add 2nd record
rs("ID") = 2
rs("Field1") = "D"
rs("Field2") = "C"

' Print original recordset
Debug.Print "Original Recordset:"
Debug.Print "====================="

While Not rs.EOF
Debug.Print rs("Field1") & vbTab & rs("Field2")


' You get error 3001 on the following line
rs.Filter = " Field1 > Field2 "

' To demonstrate the workaround, comment the preceding line and
' uncomment the following line:

' Call Filter
Exit Sub

Debug.Print "Error Number:", Err.Number
Debug.Print "Error Source:", Err.Source
Debug.Print "Error Description:", Err.Description
End Sub

Private Sub Filter()
Debug.Print "Filtered Recordset: "
Debug.Print "====================="

While Not rs.EOF
If rs("Field1") > rs("Field2") Then
Debug.Print rs("Field1") & vbTab & rs("Field2")
End If
End Sub
Press F5 to run the project, and error 3001 occurs.


For more information, please refer to the ADO Help documentation.
For additional information, please see the following article in the Microsoft Knowledge Base:
195222 PRB: ADO Find Method Only Supports One Criteria

ID do Artigo: 216873 - Última Revisão: 24/08/2001 - Revisão: 1