You are currently offline, waiting for your internet to reconnect

PRB: Error 3001 Comparing Fields as Criteria to Filter Recordset

This article was previously published under Q216873
Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
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.xLibrary.
  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,     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:
195222 PRB: ADO Find Method Only Supports One Criteria
Properties

Article ID: 216873 - Last Review: 08/24/2001 08:40:00 - Revision: 1.0

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

  • kbcode kbdatabase kbfix kbprb KB216873
Feedback
/html>f?DI=4050&did=1&t=">