Link to home
Start Free TrialLog in
Avatar of robdogg10
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.
Avatar of Éric Moreau
Éric Moreau
Flag of Canada image

try the substring:

rs.filter = "substring(name,1,1) = '%'"
Avatar of robdogg10
robdogg10

ASKER

You can't have functions in .Filter - it does not work
You can try following

rs.filter = "name<= '%' and name>$"
Correction
rs.filter = "name<= '%' and name>'$'"
ASKER CERTIFIED SOLUTION
Avatar of magnus23
magnus23

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
>>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.

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.



>> 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 .