Microsoft KB Archive/216873

= PRB: Error 3001 Comparing Fields as Criteria to Filter Recordset =

Article ID: 216873

Article Last Modified on 8/24/2001

-

APPLIES TO


 * Microsoft ActiveX Data Objects 2.0
 * Microsoft ActiveX Data Objects 2.01
 * Microsoft ActiveX Data Objects 2.1
 * Microsoft ActiveX Data Objects 2.1 Service Pack 2
 * Microsoft ActiveX Data Objects 2.5
 * Microsoft ActiveX Data Objects 2.6
 * Microsoft ActiveX Data Objects 2.7

-



This article was previously published under Q216873



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
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= ;User ID= ;" _ "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.

