Select Case

[dgkindy]

I am trying to use a Select Case but have come across what does not appear to be handled well. Can some one confirm how this should be handled or should I just use a IF...Then

What I want is if the Description contains the word "KIT" then that case is selected otherwise it moves on.

Description = "KIT,MEDIA, ACTIVATED CARBON FILTER"

Select Case Description
Case "KIT"
Test = "Install & Commish"
Case Else
test = "Labor"
End Select

 

[boblarson]

this looks like just a simple 2 result check; either it has Kit or it doesn't. So, why not an If Then Else using Instr function to check for the string Kit?

 

[Ratter]

Looks to me like you need the InStr function to find a string in a string and then use If Then Else. Partial matches are hard to use with Case

 

[DJkarl]

I am trying to use a Select Case but have come across what does not appear to be handled well. Can some one confirm how this should be handled or should I just use a IF...Then

What I want is if the Description contains the word "KIT" then that case is selected otherwise it moves on.

Description = "KIT,MEDIA, ACTIVATED CARBON FILTER"

Select Case Description
Case "KIT"
Test = "Install & Commish"
Case Else
test = "Labor"
End Select

I generally don't use Case statements unless I have more than 2 outcomes, in this case I would use an if/then but that's just my preference.

I would check your condition by using an Instr comparison

        Select Case InStr(1, Description, "KIT", vbTextCompare)
            Case >= 1
                Test = "Install & Commish"
            Case Else
                test = "Labor"
        End Select

 

[ajetrumpet]

Wouldn't that be a problem if the description contains a word in it with the sequence of letters "kit" inside of it?

 

[Brianwarnock]

Wouldn't that be a problem if the description contains a word in it with the sequence of letters "kit" inside of it?

Well spotted Adam, its often the simple things that are overlooked.
You would have to build in a test such as
IIf([Description] Like "*[!a-z]kit[!a-z]*","ok","not ok")

Not sure of VBA syntax.

Brian

 

[ajetrumpet]

What I want is if the Description contains the word "KIT" then that case is selected otherwise it moves on.

Description = "KIT,MEDIA, ACTIVATED CARBON FILTER"

Select Case Description
Case "KIT"
Test = "Install & Commish"
Case Else
test = "Labor"
End Select

Kindy,

You have to be a bit more specific when you say "contains the WORD KIT". From what the description says above, I would assume that the "description value" is always a bunch of words, separated by a delimiter (at least a space between them)? If you have, at the very least, a space that delimits the separate strings in the "description" value, then you would have to write this to correctly check the KIT condition (using simple "IF, THEN" conditional statements):

Description = "KIT,MEDIA, ACTIVATED CARBON FILTER"

  if description LIKE "*" & "KIT" & "*" then
    Test = "Install & Commish"

  else
    test = "Labor"

This can be done in VBA just like it's done in a query (most of the time). I gave an explantion of the use of "*" symbols in post #8 here.

 

[Brianwarnock]

Adam I don't think you example would find Kit in dgkindy's example where kit is not surriunded by spaces, my example will find Kit if it is not surrounded by alpha ie a to z, but will allow for any other character to precede or follow.

Brian

 

[ajetrumpet]

Yeah, I know Brian.

But then again, how do we know? The OP has not returned to the scene of the crime!

 

[Brianwarnock]

I do sometimes wonder if posters read the answer in the Email and don't bother to come back and ackowledge, the way round that is to use code tags but it seems unnescessary for a few simple lines of code.

Brian

 

[ajetrumpet]

Know what my favorite thing is?

When someone finds a code "template" on another website, pastes it into their own DB module, and just replaces the obvious variables with their own (if they even know what they are). Then, they post it here (it's worse if it is not enclosed in code tags!!!!), and say "Can someone help me with this?"

I answered a one-liner today by saying "YES, You can". Hey, they asked a simple question, and they got a SIMPLE answer!

And BTW Brian, I see where you're from (the "capital of Culture"??). I was in the grocery store the other day. The checker and another customer were talking about pronunciations of words in French. She asked me, "do you know if we're pronouncing that correctly?". And I said, "I work in Accounting, Lady".