Using Instr with Case

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

scott - 23 Jun 2007 18:26 GMT

I'm trying to use an "Instr" test with a case statement. Is this possible?

In my code below, I'm trying to test the first 2 characters in a string and
determine if the string begins with "f_" or "r_". Can someone take a stab at
my code? It returns "unknown" instead of "f_"?

CODE: ***************************

Public Sub TestCase()
Dim sString As String

sString = "f_test1"

Select Case sString

Case InStr(2, sString, "f_") > 0
Debug.Print "Caught f_"
Case InStr(2, sString, "r_") > 0
Debug.Print "Caught r_"
Case Else
Debug.Print "Unknown"
End Select

End Sub

Reply to this Message

Rick Brandt - 23 Jun 2007 18:44 GMT

> I'm trying to use an "Instr" test with a case statement. Is this
> possible?
[quoted text clipped - 20 lines]
>
> End Sub

Case doesn't work the way you are trying to make it. You can get the same
result with the following syntax...

Public Sub TestCase()
Dim sString As String

sString = "f_test1"

Select Case True
Case InStr(2, sString, "f_") > 0
Debug.Print "Caught f_"
Case InStr(2, sString, "r_") > 0
Debug.Print "Caught r_"
Case Else
Debug.Print "Unknown"
End Select

End Sub

Signature

Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Reply to this Message

Steve - 23 Jun 2007 18:53 GMT

How about ---
Dim sString As String
Dim FirstLetter As String
sString = "f_test1"
FirstLetter = Left(sString,1)
If FirstLetter = "f" Then
MsgBox "Caught f"
ElseIf FirstLetter = "r" Then
MsgBox "Caught r"
Else
MsgBox "Unknown"
End If

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
resource@pcdatasheet.com

> I'm trying to use an "Instr" test with a case statement. Is this possible?
>
[quoted text clipped - 20 lines]
>
> End Sub

Reply to this Message

John Marshall, MVP - 23 Jun 2007 19:19 GMT

How about actually reading the question? The OP was looking for the first
TWO characters.

John... Visio MVP

> How about ---
> Dim sString As String
[quoted text clipped - 10 lines]
>
> PC Datasheet

Reply to this Message

Steve - 23 Jun 2007 20:46 GMT

Read the question, f____r , the Op is looking to see if "f_test1" begins
with an "f" or "r" just like the code you suggested.

> How about actually reading the question? The OP was looking for the first
> TWO characters.
[quoted text clipped - 15 lines]
>>
>> PC Datasheet

Reply to this Message

John Marshall, MVP - 23 Jun 2007 21:02 GMT

Cute, you were able to use the three characters in a word. Since you have a
severe inability to read or understand, the OP said:

> I'm trying to test the first 2 characters in a string and determine if the
> string begins with "f_" or "r_".

The underscore at the end is a character and is part of the required test.
Otherwise words like "failure", "ridiculous" would pass the test.

If this is your level of accuracy, then you are definitely over charging,
especially for your "FREE" service. So isn't it time for you to dissappear
for the rest of the year?

John... Visio MVP

For the casual lurker, steve only attempts to answer questions in these
newsgroups so he can con unsuspecting users into paying for his questionalbe
services. He claims he has thousands of satisifed customers, but none have
ever come to his defense.

> Read the question, f____r , the Op is looking to see if "f_test1" begins
> with an "f" or "r" just like the code you suggested.
[quoted text clipped - 18 lines]
>>>
>>> PC Datasheet

Reply to this Message

StopThisAdvertising - 23 Jun 2007 21:03 GMT

> Read the question, f____r , the Op is looking to see if "f_test1" begins
> with an "f" or "r" just like the code you suggested.

Starting to get angry Steve ...??
Just read more carefully I would suggest, no reason to get upset...

BTW: 149 pageloads only the last three days

ArnoR

Reply to this Message

StopThisAdvertising - 23 Jun 2007 19:38 GMT

> How about ---
> Dim sString As String
[quoted text clipped - 8 lines]
> MsgBox "Unknown"
> End If

PC Datasheet
Providing Customers Wromg Answers For Help With Access, Excel And Word
Applications

Signature

You are *not* a resource at all !!
Tell us you will stop advertising here, or get lost for another year or so...
http://home.tiscali.nl/arracom/whoissteve.html

ArnoR

Reply to this Message

John Marshall, MVP - 23 Jun 2007 19:17 GMT

The InStr function finds if the test string is anywhere in the string and
the first parameter, in your case 2, tells where to start looking. So it is
not what you need.

Change your code to:

Dim sString As String
Dim tmpstring As String

sString = "f_test1"

tmpstring = Left(sString, 2)
Select Case tmpstring

Case "f_"
Debug.Print "Caught f_"
Case "r_"
Debug.Print "Caught r_"
Case Else
Debug.Print "Unknown"
End Select

John... Visio MVP

> I'm trying to use an "Instr" test with a case statement. Is this possible?
>
[quoted text clipped - 20 lines]
>
> End Sub

Reply to this Message

scott - 23 Jun 2007 19:36 GMT

works great!

> The InStr function finds if the test string is anywhere in the string and
> the first parameter, in your case 2, tells where to start looking. So it
[quoted text clipped - 45 lines]
>>
>> End Sub

Reply to this Message