Improve company productivity with a Business Account.Sign Up

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

VB 2005 Analog to VBA Instr() Function

I have this cool little form in an Access app that allows the user to use sort of a "Thumb Index" to filter a large file of customers. There are 27 buttons - one for each letter, and one with a pound sign on it for customers with names like "123 Plumbing" or "1-800-Ask Gary."

Getting the sql to filter on the first letter is easy. But to get all the clients whose names start with a non-letter, I used this:

If strAlpha = "#" Then 'Number Picked
            strCritString = " WHERE InStr('ABCDEFGHIJKLMNOPQRSTUVWXYZ', UCase(Left([Accounts]![Customer],1))) = 0 "
           
        Else 'Letter Picked
            strCritString = " WHERE UCase(Left([Accounts]![Customer],1))='" & strAlpha & "'"
        End If

Evidently the InStr function in VB 2005 functions a little differently. I just want to be able to test for the case where the "strAlpha" variable is not one of the 26 letters.

THANKS!
0
Buck_Beasom
Asked:
Buck_Beasom
  • 4
  • 3
  • 2
  • +1
1 Solution
 
LVMB1417Commented:
Substring is what you are looking for

Dim s as String

s = "abcdefghijklmnopqrstuvwxyz"

console.writeline s.Substring(2,1)  

2 is the starting position
1 is the length you want to take

s.substring(2,1) = b
s.substring(2,3) = bcd

etc...



0
 
CodeCruiserCommented:
The other option is Not In
" WHERE  UCase(Left([Accounts]![Customer],1)) Not In ('A', 'B', ...) = 0 "
0
 
CodeCruiserCommented:
The other option is Not In
" WHERE  UCase(Left([Accounts]![Customer],1)) Not In ('A', 'B', ...)"
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
ZhaolaiCommented:
Try this:
If Char.IsNumber(strAlpha.Substring(0, 1)) Then 'Number Picked

Open in new window

0
 
Buck_BeasomDatabase DesignerAuthor Commented:
OK. I am working remote from my house on my work computer, so this is a little difficult to test. But here goes.

LVMB's solution is valuable information, but doesn't get me where I want. If I interpret Zhaolai correctly,  this would work, but only if the 1st character of the customer name is a number. I also have all kinds of other bizarre cases like +, /, # and so on. Some are actually names that begin with those characters and sometimes it's just a typo. (I even have spaces, in a few cases.)

So it looks like Codecatcher's solution is best for my specific need, but I need to confirm. If I continue the SECOND solution you propose so that there are 26 arguments in the "Not In" function - basically each of the 26 regular letters of the alphabet - I'm guessing that will return a list of all those customers that start with a character that is Not In ABCDEFGHIJKLMNOPQRSTUVWXYZ. Of course, I have to list each letter in ' ' and separated by a comma.

Right? (Or is it the first solution?)

THANKS!
0
 
ZhaolaiCommented:
If you want anything not starting with a letter, then try this:
If Not Char.IsLetter(strAlpha.Substring(0, 1)) Then 'Not a Number Picked

Open in new window

0
 
CodeCruiserCommented:
I think the second option is right for you and yes you will have to add all the alphabets in '' in the "Not In" clause.

My solution is T-SQL based whereas the other solutions are .NET based and I dont know how this line will help you

"If Not Char.IsLetter(strAlpha.Substring(0, 1))"
0
 
Buck_BeasomDatabase DesignerAuthor Commented:
Zhaolai:

That seems a more elegant solution. But I am using the test in an SQL criteria string. So I need something like "WHERE Char.Isletter(UCase(Left([Accounts]![Customer],1))) = False". I will try your solution and see if the SQL gets interpreted properly.

Thanks.
0
 
Buck_BeasomDatabase DesignerAuthor Commented:
After trying all of the various permutations, this was the one that functioned the way I wanted it to in the SQL string.

Thanks!
0
 
CodeCruiserCommented:
Glad to help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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