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

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 829
  • Last Modified:

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.
0
robdogg10
Asked:
robdogg10
  • 4
  • 4
  • 3
2 Solutions
 
Éric MoreauSenior .Net ConsultantCommented:
try the substring:

rs.filter = "substring(name,1,1) = '%'"
0
 
robdogg10Author Commented:
You can't have functions in .Filter - it does not work
0
 
magnus23Commented:
You can try following

rs.filter = "name<= '%' and name>$"
0
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.

 
magnus23Commented:
Correction
rs.filter = "name<= '%' and name>'$'"
0
 
magnus23Commented:
Sorry one more time :)
rs.filter = "name> '%' and name<'&'"
0
 
robdogg10Author Commented:
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?
0
 
Éric MoreauSenior .Net ConsultantCommented:
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?
0
 
robdogg10Author Commented:
>>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.

0
 
Éric MoreauSenior .Net ConsultantCommented:
Isn't "oRs.Filter = " name > '*' and name < '+' "" also a hack?
0
 
magnus23Commented:
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.



0
 
robdogg10Author Commented:
>> 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 .
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 4
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now