PRB: Err 3001 Comparing Fields as Criteria to Filter Recordset

ID: Q216873

The information in this article applies to:

ActiveX Data Objects (ADO), versions 2.0, 2.01, 2.1, 2.1 SP2

SYMPTOMS

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.

CAUSE

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.

RESOLUTION

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.

STATUS

This behavior is by design.

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
===========================

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

Set a Project Reference to the Microsoft ActiveX Data Objects 2.0 Library.

Insert a command button on the form. Command1 is created by default.

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=ammabu;User ID=sa;" _ 
    "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, 
    adLockOptimistic
       ' Add first record
       rs.AddNew
       rs("ID") = 1
       rs("Field1") = "A"
       rs("Field2") = "B"
       rs.Update
       rs.Requery
   
       ' Add 2nd record
       rs.AddNew
       rs("ID") = 2
       rs("Field1") = "D"
       rs("Field2") = "C"
       rs.Update
       rs.Requery
    
       ' Print original recordset
       Debug.Print "Original Recordset:"
       Debug.Print "====================="

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

       rs.MoveFirst

       ' 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

   errh:
       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
         rs.MoveNext
       Wend
   End Sub

Press F5 to run the project and error 3001 occurs.

REFERENCES

For more information, please refer to the ADO Help documentation.
For additional information, please see the following article in the Microsoft Knowledge Base:

Q195222 PRB: ADO Find Method Only Supports One Criteria

Additional query words: kbADO kbADO200 kbVBp kbcode

Keywords : kbADO kbADO200 kbDatabase kbVBp kbGrpVBDB kbGrpMDAC kbDSupport kbADO210sp2
Version : WINDOWS:2.0,2.01,2.1,2.1 SP2
Platform : WINDOWS
Issue type : kbprb