Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Recordset.Filter dilemma

Posted on 2003-11-24
11
822 Views
Last Modified: 2013-12-25
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
Comment
Question by:robdogg10
  • 4
  • 4
  • 3
11 Comments
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 9816848
try the substring:

rs.filter = "substring(name,1,1) = '%'"
0
 
LVL 1

Author Comment

by:robdogg10
ID: 9819619
You can't have functions in .Filter - it does not work
0
 
LVL 3

Expert Comment

by:magnus23
ID: 9820660
You can try following

rs.filter = "name<= '%' and name>$"
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
LVL 3

Expert Comment

by:magnus23
ID: 9820668
Correction
rs.filter = "name<= '%' and name>'$'"
0
 
LVL 3

Accepted Solution

by:
magnus23 earned 50 total points
ID: 9820713
Sorry one more time :)
rs.filter = "name> '%' and name<'&'"
0
 
LVL 1

Author Comment

by:robdogg10
ID: 9821487
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
 
LVL 70

Assisted Solution

by:Éric Moreau
Éric Moreau earned 30 total points
ID: 9821604
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
 
LVL 1

Author Comment

by:robdogg10
ID: 9822062
>>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
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 9822158
Isn't "oRs.Filter = " name > '*' and name < '+' "" also a hack?
0
 
LVL 3

Expert Comment

by:magnus23
ID: 9823832
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
 
LVL 1

Author Comment

by:robdogg10
ID: 9826509
>> 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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Suggested Solutions

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
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…
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

809 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