LeifB
asked on
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 ?
rst.filter = "[colname1]=[colname2]"
anyone help ?
ASKER
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.
There is no query, I use ADO recordset offline instead of arrays as generic datastore.
You may be able to do it like this.
rst.filter = "[colname1]=" & rst("colname2")
rst.filter = "[colname1]=" & rst("colname2")
ASKER
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 ...
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 ...
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.....
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
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
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...
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...
ASKER
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.
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.
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.
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.
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").
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.....
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.....
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
acekf, try using MDAC 2.7 SP1.
ASKER
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
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
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.
ASKER
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.
rst.filter = "colname1=colname2"
Otherwise:
Why don't you do it in the query self ?
Rs.open "select * from yourtable where colname1 = colname2"