Solved

ado VB recordset filter question

Posted on 2004-10-07
16
404 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

734 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