Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

ado VB recordset filter question

Posted on 2004-10-07
16
Medium Priority
?
413 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
Industry Leaders: 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 500 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

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.

Question has a verified solution.

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

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

926 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