Solved

ado VB recordset filter question

Posted on 2004-10-07
16
408 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
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…
Suggested Courses
Course of the Month4 days, 3 hours left to enroll

630 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