Solved

ado VB recordset filter question

Posted on 2004-10-07
16
398 Views
Last Modified: 2010-05-18
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 ?
0
Comment
Question by:LeifB
  • 6
  • 5
  • 3
  • +2
16 Comments
 
LVL 53

Expert Comment

by:Dhaest
ID: 12248278
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
 
LVL 1

Author Comment

by:LeifB
ID: 12248339
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
 
LVL 7

Expert Comment

by:a1programmer
ID: 12248783
You may be able to do it like this.

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

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:LeifB
ID: 12249106
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
 
LVL 17

Expert Comment

by:inthedark
ID: 12250610
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
 
LVL 17

Expert Comment

by:inthedark
ID: 12250665
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
 
LVL 7

Expert Comment

by:a1programmer
ID: 12250710
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
 
LVL 1

Author Comment

by:LeifB
ID: 12250971
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
 
LVL 7

Expert Comment

by:a1programmer
ID: 12251031
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
 

Expert Comment

by:acekf
ID: 12251163
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
 
LVL 17

Expert Comment

by:inthedark
ID: 12251189
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
 
LVL 17

Accepted Solution

by:
inthedark earned 125 total points
ID: 12251336
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
 
LVL 17

Expert Comment

by:inthedark
ID: 12251369
acekf, try using MDAC 2.7 SP1.
0
 
LVL 1

Author Comment

by:LeifB
ID: 12256597
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
 
LVL 17

Expert Comment

by:inthedark
ID: 12256689
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
 
LVL 1

Author Comment

by:LeifB
ID: 12274602
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

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question