robdogg10
asked on
Recordset.Filter dilemma
Ok, this one is driving me crazy. I have a disconnected recordset. The recordset has 2 fields, 'ID' and 'NAME'. I want to find in all the records where the value for the NAME field starts with % (percent sign). The percent sign is also a reserved word in ADO SQL.
So I tried the following:
rs.Filter = "NAME LIKE '%'"
This returned everything.
So I tried to escape the percent sign:
rs.Filter = "NAME LIKE '%'"
This was much better, but it returned all records which had the percent sign anywhere in the string.
So how can I manipulate the the .Filter property to get it to show just records that start with % (percent sign). Also, I'd like to do the same thing with the * sign (which also happens to be a reserved word in ADO SQL).
Thanks.
P.S. I know that I can loop through the recordset and find what I need by brute force, but I am looking for a .Filter solution.
So I tried the following:
rs.Filter = "NAME LIKE '%'"
This returned everything.
So I tried to escape the percent sign:
rs.Filter = "NAME LIKE '%'"
This was much better, but it returned all records which had the percent sign anywhere in the string.
So how can I manipulate the the .Filter property to get it to show just records that start with % (percent sign). Also, I'd like to do the same thing with the * sign (which also happens to be a reserved word in ADO SQL).
Thanks.
P.S. I know that I can loop through the recordset and find what I need by brute force, but I am looking for a .Filter solution.
ASKER
You can't have functions in .Filter - it does not work
You can try following
rs.filter = "name<= '%' and name>$"
rs.filter = "name<= '%' and name>$"
Correction
rs.filter = "name<= '%' and name>'$'"
rs.filter = "name<= '%' and name>'$'"
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Magnus23,
Your trick of going to the next letter in the ASCII alphabet worked. But I can't make it work if I am looking for everything starting with *
I tried this:
oRs.Filter = " name > '*' and name < '+' "
But it also returned items starting with @
Any wisdom on this?
Your trick of going to the next letter in the ASCII alphabet worked. But I can't make it work if I am looking for everything starting with *
I tried this:
oRs.Filter = " name > '*' and name < '+' "
But it also returned items starting with @
Any wisdom on this?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
>>When you launch your query to fill your recordset, why don't you add a dummy column containing the substring(name,1,1) and filter on this new column?<<
Because that would be a hack.
Because that would be a hack.
Isn't "oRs.Filter = " name > '*' and name < '+' "" also a hack?
robdogg10
Both solitions, my and from emoreau is a workarounds, there is no stright way to acomplish this. Usually recordset filtered and sorted on a stage of getting the data, so now it's just a tricks.
Ifyou need to look for a different char then you just have to create something like lookup table, where you will have all simbols less then the one that you need and bigger.
For current situation i can assume that you need to compare like this
rs.filter = "name> '*' and name<')'"
Actually it is the same order of simbols in a character codes these two have 42 and 41 respectivly.
Both solitions, my and from emoreau is a workarounds, there is no stright way to acomplish this. Usually recordset filtered and sorted on a stage of getting the data, so now it's just a tricks.
Ifyou need to look for a different char then you just have to create something like lookup table, where you will have all simbols less then the one that you need and bigger.
For current situation i can assume that you need to compare like this
rs.filter = "name> '*' and name<')'"
Actually it is the same order of simbols in a character codes these two have 42 and 41 respectivly.
ASKER
>> Isn't "oRs.Filter = " name > '*' and name < '+' "" also a hack? <<
It is, but adding an extra field for the purpose of the hack is a bigger hack, though it may in the end give you cleaner code. It is a toss-up.
Gentlemen,
Thank you both, I'll increase the points (whatever little I have) and split them .
It is, but adding an extra field for the purpose of the hack is a bigger hack, though it may in the end give you cleaner code. It is a toss-up.
Gentlemen,
Thank you both, I'll increase the points (whatever little I have) and split them .
rs.filter = "substring(name,1,1) = '%'"