Using CASE in VBA

[Oreynolds]

Hi,

I have a module that within it I have to use the same CASE argument several times as it loops through a number of different processes as per below:

         Select Case rstDetail.Fields("CategoryID")
                    Case 20, 32, 33, 34, 35, 36, 37, 56, 58, 59, 60, 125

There is the potential that this may need adjusting in the future so rather than having to adjust the above case number string several times I was hoping to be able to declare the list once and then reference to it each time its used, something like the below:

Dim strCase As String

strCase = "20, 32, 33, 34, 35, 36, 37, 56, 58, 59, 60, 125"

      Select Case rstDetail.Fields("CategoryID")
      Case strCase

Understandably this doesn't work as strCase is declared as a text string and it needs to be numbers. I have tried a few other options but none have worked. Is there any way of achieving this?

 

[June7]

Could have a table of these specific values and use DLookup() or DCount(). This would allow user to modify the set.

 

[ebs17]

strCase = "|20|32|33|34|35|36|37|56|58|59|60|125|"
Select Case True
   Case Instr(1, strCase, "|" & rstDetail.Fields("CategoryID") & "|") > 0
' ...

 

[MajP]

If these numbers are likely to change then I would store them in a table not in code. That looks like data and data is stored in tables not in code. Then forget the case and just do a dcount to see if it is in the table

 

[The_Doc_Man]

Your problem is the form of the CASE option list.

Select Case statement (VBA)

Office VBA reference topic

learn.microsoft.com

You can have quoted strings as case options and can have comma-separated lists. The problem is that {strCase = "20, 32, 33, 34, 35, 36, 37, 56, 58, 59, 60, 125"} offers only a single string with commas inside. I don't think concatenation is going to help.

Therefore, in VBA the method suggested by Eberhard (ebs17) will work.

IF you needed this in SQL, then making it a list in a table would work using a clause like

WHERE MyNumber IN (SELECT Choices FROM MyNumberLIst)

If you went to the trouble to build the list, then you could use this in VBA:

DCOUNT( "MyNumber", "MyNumberList", "MyNumber=" & targetnumber )

Where the count is either 0 (not in the list) or 1 (in the list)

 

[Oreynolds]

strCase = "|20|32|33|34|35|36|37|56|58|59|60|125|"
Select Case True
   Case Instr(1, strCase, "|" & rstDetail.Fields("CategoryID") & "|") > 0
' ...

Thanks so much works perfectly

 

[Gasman]

I would be with post #4?