Select Case Wildcard

[Charles D Clayton Jr]

I need to organize a list of three digit numbers into one of five
categories:
"#11", "#12", "#13", "#3#", "#4#" with the # representing a wildcard.
I thought it would be easy. Just write a function and then call it
from a query but I guess you cannot use a wildcard in the Select Case
expression. Here is what I originally had:

Function Psort (A as Integer)
Select Case PID = "#11"
Progress = 1
Select Case PID = "#12"
Progress = 2
Select Case PID = "#13"
Progress = 3
Select Case PID = "#3#"
Progress = 4
Select Case PID = "#4#"
Progress = 5
Case Else
Progress = 9999


I was able to change the first three lines and get them to work but
not the last two.

Select Case PID = 111,211,311,411,511,611,711,811,911

Does anyone have any suggestions? Maybe I need to be thinking about
something completely different.

Thanks,

Charles D Clayton Jr

 

[Chadlon]

Function Psort (A As Integer)
Dim wk$, i%
wk$ = Format$(A,"000")
Psort = 1: If 2 = Instr(2,wk$, "11") Then Exit Function
Psort = 2: If 2 = Instr(2,wk$, "12") Then Exit Function
Psort = 3: If 2 = Instr(2,wk$, "13") Then Exit Function
Psort = 4: If 2 = Instr(2,wk$, "3") Then Exit Function
Psort = 5: If 2 = Instr(2,wk$, "4") Then Exit Function
Psort = 9999
End Function

 

[Douglas J. Steele]

Your syntax for using SELECT CASE is incorrect.

The proper syntax is:

Select Case testexpression
[Case expressionlist-n
[statements-n]] ...
[Case Else
[elsestatements]]
End Select

or something like:

Select Case Number
Case 1 To 5
Debug.Print "Between 1 and 5"
Case 6, 7, 8
Debug.Print "Between 6 and 8"
Case Is > 8 And Number < 11
Debug.Print "Greater than 8"
Case Else
Debug.Print "Not between 1 and 10"
End Select

Now, I'm not sure from your example what the relationships between A and PID
is, nor what Progress is supposed to represent. Here's how you'd write a
function to return 1 if the argument ends in 11, 2 if the argument ends in
12, 3 if the argument ends in 13, 4 if the 2nd last digit of the argument is
3, 5 if the 2nd last digit of the argument is 4 and 9999 otherwise.

Function PSort(A As Integer) As Integer

Dim intLastTwoDigits As Integer

intLastTwoDigits = A - (A \ 100) * 100

Select Case intLastTwoDigits
Case 11
PSort = 1
Case 12
PSort = 2
Case 13
PSort = 3
Case Is >= 30 And intLastTwoDigits <= 39
PSort = 4
Case Is >= 40 And intLastTwoDigits <= 49
PSort = 5
Case Else
PSort = 9999
End Select

End Function

 

[Allen Browne]

Probably easier to code with ElseIf:

If PID Like "?11" Then
Progress = 1
ElseIf PID Like "?12" Then
Progress = 2
ElseIf PID Like "?13" Then
Progress = 3
ElseIf PID Like "?3?" Then
Progress = 4
ElseIf PID Like "?4?" Then
Progress = 5
Else
Progress = 9999
End If

If you do want to persist with Select case, note that the word "Select" does
not appear on each "Case" line.

 

[Cheryl Fischer]

Charles,

First of all, you cannot use Like in a Case Statement structure, which
requires exact matches. You could, however, use an If ..
ElseIf...Else...EndIf.

For example:

Function Psort (A as Integer) as Integer

If CStr(A) like "#11" then
Psort = 1
ElseIf CStr(A) like "#12" then
Psort= 2
ElseIf CStr(A) like"#13" then
Psort= 3
ElseIf CStr(A) like "#3#" then
Psort = 4
ElseIf CStr(A) like "#4#" then
Psort = 5
Else
Psort = 9999
End If

End Function


I have changed the function above because you are passing in an Integer as
the argument, A, yet you are evaluating Strings to determine the function's
return value.

hth,

 

[Charles D Clayton Jr]

I just want to thank everyone for their wonderful solutions. I have
learned a few things and I do greatly appreciate your time and effort.

Blessings,

Charles D Clayton Jr

 

[Cheryl Fischer]

You're welcome, from all of us!