How do I use a passed parameter in a Case statement

[tarenja]

I have a function that is successfully being passed a list of numbers into a variable called ActivityIDs (typically like 7,8,21,28).
I want to use this variable in a Case statement but I can'ft figure out how.

I've used the case statement in the more "traditional" way
Select Case rsA(0)
Case 7,8,21,28
Do some stuff
End Select
and it works fine but whan I try to use the variable as below it won't work

Select Case rsA(0)


Case ActivityIDs
Do some stuff
End Select
When I debug.print ActivityIDs it looks correct
Any help would be appreciated

This is what I'm passing: =getActivityNames([tblDelegates.DelegateID],"7,8,21,28")
I just figured out the numbers are being passed as numbers; I thought they would come in as "7,8,21,28", instead they are coming as 782128.
Can anyone advise me how to get them in as I was wanting please?

[June7]

The value passed is a single string, not a list of distinct items. What causes the commas to drop out?

The only way I know to pass a list is as an array, use Array() function. Then loop through the array elements.

[JrMontgom]

What do you need the program to do? DO you want the program to , in the Select Case statement. to look at every ID and do something different? If so use the stirng function Split to separate the string into a string array and use Case "7" ,,,Case "19" do what ever. Use the Help to see how to use Split.

[tarenja]

Thanks for the responses gents.
I've played around some more but still can't figure it out.
The values coming in to the function as displyed in the debug window are Passed activityIDs = 7,8,21,28
These are coming in as a string tagged ActivityIDs
What I want to do is use these values in a select Case as follows
Select Case rsB(1)
'Case 7, 8, 9, 10
Case ActivityIDs
Debug.Print rsB(0), " ", rsB(1)
End Select
In the example above the Case 7, 8, 9, 10 works but Case ActivityIDs does not.
I need to know how to get the case to see inside "ActivityIDs" and act on the values

[June7]

Will there always be only 4 elements?
Does this do what you want:

Dim aryIDs As Variant 'declare an array variable
aryIDs = Split(ActivityIDs, ",") 'populate array with Split function
Select Case rsB(1)
Case aryIDs(0), aryIDs(1), aryIDs(2), aryIDs(3)
Debug.Print rsB(0), " ", rsB(1)
End Select

If the number of elements can vary then that code won't work. Will have to loop through the array and compare each element with rsB(1) with an If Then conditional.

[tarenja]

Will there always be only 4 elements?
Does this do what you want:

Dim aryIDs As Variant 'declare an array variable
aryIDs = Split(ActivityIDs, ",") 'populate array with Split function
Select Case rsB(1)
Case aryIDs(0), aryIDs(1), aryIDs(2), aryIDs(3)
Debug.Print rsB(0), " ", rsB(1)
End Select

If the number of elements can vary then that code won't work. Will have to loop through the array and compare each element with rsB(1) with an If Then conditional.

Thanks for that.
I just logged in to say I pretty much figured out what you have said.
I now at least understand what's going on.
I can now get it to work with a set number of elements but, as you ask, the function will receive varying numbers of elments.
I'll plod on and see what I can come up with.

[tarenja]

I'm not sure if there's a more eleant way but the following at least works.
Thanks for your help.

If Not (rsB.BOF = True And rsB.EOF = True) Then
rsB.MoveLast
rsB.MoveFirst
Do Until rsB.EOF
For ctr = 0 To UBound(sparts) - 1
If sparts(ctr) = rsB(1) Then Debug.Print rsB(0), " ", rsB(1)
Next ctr
rsB.MoveNext
Loop
End If

[June7]

For variable number of elements:

Dim aryIDs As Variant 'declare an array variable
Dim i as Integer
aryIDs = Split(ActivityIDs, ",") 'populate array with Split function
For i = 0 to UBound(aryIDs) - 1
If aryIDs(i) = rsB(1) Then
'do something
End If
Next