ado VB recordset filter question

trying to filter records from a recordset that have one columne equal to another. Looks like .filter cant take more than one column name though. This wont work (error 3001):

rst.filter = "[colname1]=[colname2]"

anyone help ?
LVL 1
LeifBAsked:
Who is Participating?
 
inthedarkConnect With a Mentor Commented:
Error 3001 just won;t go away, I see what you mean but......

Looping and a comapre would be very fast in a disconnected recordset. Way fater than if it was saved onto a database.

The way the disconnected recordset works is that it is just dressed up array. A looping solution, like below, will be real fast.

Private Sub Form_Load()

'create a recordset
Dim RS As ADODB.Recordset
Set RS = New ADODB.Recordset

' create some fields
RS.Fields.Append "Col1", adInteger
RS.Fields.Append "Col2", adInteger

' get the set ready for action
Set RS.ActiveConnection = Nothing
RS.Open

' add some test data
RS.AddNew
RS("col1") = 1
RS("col2") = 1
RS.Update

RS.AddNew
RS("col1") = 2
RS("col2") = 3
RS.Update

RS.AddNew
RS("col1") = 4
RS("col2") = 4
RS.Update

'=============
' looping solution
RS.MoveFirst
Do While Not RS.EOF
    If RS("col1") = RS("col2") Then ' this will be fast even for thousands of records.
        Debug.Print "" & RS("Col1") & "  " & RS("Col2")
    End If
    RS.MoveNext
Loop

RS.Close
Set RS = Nothing

End Sub


But if you need to recall the rs many times then I can suggest another quick way, when you create or update each record, use and extra field to store a compare of the fields:

' need an extra fields
RS.Fields.Append "Compare", adBoolean

' When you add a field
RS("Compare") = cBool(RS("Col1") = Rs("Col2"))

' now you can filter
RS.Filter ="(Compare=True")
0
 
DhaestCommented:
Did you try:
rst.filter = "colname1=colname2"

Otherwise:
Why don't you do it in the query self ?
Rs.open "select * from yourtable where colname1 = colname2"
0
 
LeifBAuthor Commented:
sure,did try that. Just put brackets around to indicate its the column names as you would do in SQL.

There is no query, I use ADO recordset offline instead of arrays as generic datastore.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
a1programmerCommented:
You may be able to do it like this.

rst.filter = "[colname1]=" & rst("colname2")

0
 
LeifBAuthor Commented:
rst.filter = "[colname1]=" & rst("colname2"):

Note that rst("colname2") is evaluated at the current record and set to a string. the filter will then result in all records where col1 is equal to that string, it will not compare col1 and 2 for each record. So that wont help

I cna of course loop all records and collect those where rst("colname1") = rst("colname2") afterwards. I just hoped .filter would save me that ...
   
0
 
inthedarkCommented:
I created a sort and filter gizmo for recordset to overcome filter problem. But I think that MS have now enhanced this on later versions of ADODB. I will check, back later.....
0
 
inthedarkCommented:
Yes MS have fixed it. I am using MDAC 7.1....here is an example of a double filter:

Private Sub Form_Load()

'create a recordset
Dim RS As ADODB.Recordset
Set RS = New ADODB.Recordset

' create some fields
RS.Fields.Append "Key", adVarChar, 20
RS.Fields.Append "Source", adVarChar, 1
RS.Fields.Append "Count", adInteger


' get the set ready for action
Set RS.ActiveConnection = Nothing
RS.Open

Dim lC As Long

' Create some test records
For lC = 1 To 10
    RS.AddNew
    RS("Key") = "Key" + CStr(lC)
    RS("Source") = "1"
    RS.Update
Next lC

' Create some duplicates missing every other record
For lC = 1 To 10 Step 2
    RS.AddNew
    RS("Key") = "Key" + CStr(lC)
    RS("Source") = "2" '' different souce code
    RS("Count") = 0
    RS.Update
Next lC


' sort the recordset by the keys to be filtered
RS.Sort = "Key,source"

' apply the filter
RS.Filter = "(([Key]='Key1') And ([Source]='2'))"
RS.MoveFirst
Do While Not RS.EOF
    Debug.Print RS("Key") + "  " + RS("Source")
    RS.MoveNext
Loop

' job done
RS.Close
Set RS = Nothing

End Sub
0
 
a1programmerCommented:
k, sorry. Didn't read your origonal post.

Like Dhaest  said...  Why don't you do it in the query self ?

select distinct a.* from mytable a, mytable b where a.column1 = b.column2

where a and b are the same table...
0
 
LeifBAuthor Commented:
Thanks inthedark but that the and clause isnt the problem. I may have explained this poorly so consider this table with 2 cols

col1          co2
1              1
2              3
3              2
4              4
5              1

the .filtered table should look like:
1              1
4              4

the .filter = "col1 = col2" doesnt work. seems like the right side of the "=" takes only strings. Yes you can do a synthetic "IN(...;...) but the list is too long. So can you do this filter somehow without looping ?

a1programmer that would be the best thing I agree but i am not using a database but the recordset is just an alternative to an array of structs to hold mixed data types because i like the sort, filter, find functionality you get for free.

0
 
a1programmerCommented:
Ah, I see...

Well then...  I guess you'd have to traverse through the list, keep a list of what's in both, and then set the filter such as

filter = "col1 in ( " & theList & ")"

Not sure if that's the exact syntax.
0
 
acekfCommented:
What version of ado should a person be using. I am using ado 2.5 and can navagate thru the record set , but I can only view the first record set.. I am looping throu the correct number of records in the recordset.. any ideas ... why I can not see the next record detail info. ( there is a ".movenext").
0
 
inthedarkCommented:
I will think about what you want to do.....

But one way you can do this is to connect and save your recordset, then run a query on the results.

Set RS.ActiveConnection = MyCN
RS.UpdateBatch

Back later.....
0
 
inthedarkCommented:
acekf, try using MDAC 2.7 SP1.
0
 
LeifBAuthor Commented:
a1programmer: yes that is the SQL IN clause. One can use them but comp time goes up exponentially (x^N) with each new element N I think.

inthedark: aye thanks that was what I am doing so far. It is bulky and about as elegant as towing yourself to a bus instead of riding it ><

still hoping for the .filter
0
 
inthedarkCommented:
But if you look at the raw machine code that is used, a filter would do just the same as a loop and a bit more, so becuase the loop is just increamenting a row number and the compare is just accessing 2 elements of a super variant array, it just can't be asfter to do it any other way.
0
 
LeifBAuthor Commented:
Looks like there is no way to this with filter so I will have to loop. Thanks all for participating. Points to inthedark for taking the time to analyse.
0
All Courses

From novice to tech pro — start learning today.