Select Case Wildcard

  • Thread starter Thread starter Charles D Clayton Jr
  • Start date Start date
C

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
 
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
 
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
 
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.
 
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,
 
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
 
Back
Top