Using Select Case with Strings (1 Viewer)

[shadow9449]

I see in the VBA documentation that you cannot use Like as a comparison operator as the test expression in a select statement.

What I want to do is something like this:

Select Case rst!txtMyText
  Case Like "apples*"
  ...
  Case Like "oranges*"
  ...
End Select

Is there a right way to do this or am I stuck with If statements?

Thanks

SHADOW

 

[boblarson]

Yes, you can't use Like in a Case Statement.

 

[shadow9449]

Yes, you can't use Like in a Case Statement.

So it's multiple Ifs for me...?

SHADOW

 

[boblarson]

So it's multiple Ifs for me...?

SHADOW

Well, what exactly are you needing? There are probably other ways to get it, but specific examples of what you are needing would be helpful.

 

[shadow9449]

Ok, it's a bit complicated, but you asked!

I have a table that logs interaction with clients. So, the fields would be something like:

ID
ClientID
Date
Description

The description would be text such as "Client called to complain about..." or "Client purchased...", "Client returned product..." and so on including detail. The text in the log is generated by the program so the first few words will be very consistent.

I am trying to build what I am calling a "Client Browser". It's a treeview control that allows you to have an overall look at a client's history. So, the top node is the client's name, and then a child node for the client's purchases, and then another client node for the client's log.

The children nodes would consist of incidents from the log. I want to have the date of the incident and then a brief description of the interaction in the log such as:

- 11/4/07 Client Complaint
- 05/19/08 Purchase
- 01/11/08 Return

That way you see a great overview of the log.

My design is to create a function that gets passed the ID of each description entry related to this client in the log. The function examines the log entry and passes back an appropriate string. So, if the entry said "Client called to complain that his widget was the wrong colour", the function uses a Like "Client called to complain*" to return the string "Client complaint". Of course if I have about 8 possible strings that the function could return, then a Select Case would be the best approach.

There are other approaches but I'm not certain what the best would be. It sounds like a Select is out, so a bunch of Ifs may be in order, or to write a query with nested IIfs may be possible but would be ugly to read and a nightmare to debug.

Any thoughts?

SHADOW

 

[Banana]

It would probably be most simplest to add a extra column in the table to tag the incident. Call it, say, "Cause", and limit to a choice of "Complaint", "Return"... etc then look up those to classify the incidents accordingly.

If you can't change the design, however, I would probably do this in a query:

SELECT
   Switch(
     [Description] Like "*Complaint*", "Complaint",
     [Description] Like "*Return*", "Return"
   ) AS Cause
FROM aTable;

HTH.

 

[boblarson]

Personally, I would have a set of standard "TYPES" that the users select from in ADDITION to entering the description. Then you can filter on those instead.

But, if you are going to go this other way, then you might write a custom function to do the work. But I don't relish that as being very fun.

 

[shadow9449]

It would probably be most simplest to add a extra column in the table to tag the incident. Call it, say, "Cause", and limit to a choice of "Complaint", "Return"... etc then look up those to classify the incidents accordingly.

I've considered this approach as one possible solution. It would probably be best to avoid adding a new field if the ONLY use of the field would be to tag the category of the field.

If you can't change the design, however, I would probably do this in a query:

SELECT
   Switch(
     [Description] Like "*Complaint*", "Complaint",
     [Description] Like "*Return*", "Return"
   ) AS Cause
FROM aTable;

HTH.

Actually, I've never heard of the Switch keyword as a SQL command. (The last time I used a Switch was when I used to program in C, but that's another story). I will definitely look it up and that looks like the best solution!

Thanks a lot

SHADOW

 

[shadow9449]

Personally, I would have a set of standard "TYPES" that the users select from in ADDITION to entering the description. Then you can filter on those instead.

But, if you are going to go this other way, then you might write a custom function to do the work. But I don't relish that as being very fun.

I considered those two approaches.

My hesitation of requiring the user to select a tag is that it requires user input. It's really nice that an entry into the log gets triggered by using other parts of the application without requiring the user to select something. That eliminates human forgetting and human error or carelessness.

I think that using Ifs would probably be the best way to do it in a function. It's not THAT bad but I figured that I'd see if anyone here had another way before I did it. Asking is the best way to learn, both for me and everyone else reading the forum.

Banana's approach looks like a good shot. I'm going to give it a try and post my results.

Thanks

SHADOW

 

[Banana]

I've considered this approach as one possible solution. It would probably be best to avoid adding a new field if the ONLY use of the field would be to tag the category of the field.

But the whole point of having data model is to aid in efficient retrieval and lookup. Even if that was the only use, this is going to be far more efficient and faster than doing a Switch() with multiple Like that will almost certainly requires several table scans.

Actually, I've never heard of the Switch keyword as a SQL command. (The last time I used a Switch was when I used to program in C, but that's another story). I will definitely look it up and that looks like the best solution!

Yes, for some reasons, people get fixated on nesting many Iif(), never knowing they could have had use Switch or Choose function for JET. In other RDBMS, this is probably done using CASE WHEN... , though.

Anyway, just don't be disappointed if it runs slowly. That's why we both suggested adding a field to tag the description.

Best of luck.

 

[shadow9449]

But the whole point of having data model is to aid in efficient retrieval and lookup. Even if that was the only use, this is going to be far more efficient and faster than doing a Switch() with multiple Like that will almost certainly requires several table scans.

I didn't realize that the switch would be slow. Since the application logs EVERYTHING that happens, there's going to be a LOT of entries into the log. Usually SQL is much, much faster than Dlookups or most coding solutions. I'll see what happens, though.

Yes, for some reasons, people get fixated on nesting many Iif(), never knowing they could have had use Switch or Choose function for JET.

I didn't know about those. Nested Iifs were invented by the Devil himself. (At least that's my opinion)

Anyway, just don't be disappointed if it runs slowly. That's why we both suggested adding a field to tag the description.
.

It's worth a try. At very worst I'll have learned a new SQL command that can come in handy in other situations and I'll add the tag. As I posted to Bob, the tag will only be useful if the program silently enters the tag as part of the SQL statement that adds the whole entry.

Thanks and happy new year!

SHADOW

 

[boblarson]

Making users enter a "Category" is not any harder than any other data. It is just a matter of training and also auditing data to find anyone not doing it correctly. There is no inherent badness of making them do so. I think you'll find that doing it any other way is going to get very slow when you get to thousands of records.

 

[shadow9449]

Making users enter a "Category" is not any harder than any other data. It is just a matter of training and also auditing data to find anyone not doing it correctly. There is no inherent badness of making them do so. I think you'll find that doing it any other way is going to get very slow when you get to thousands of records.

The best way is if the program enters the Type/Category when it enters the log entry. It's just another column in the same query and gets the same results without user interaction!



SHADOW

 

[Banana]

I didn't realize that the switch would be slow. Since the application logs EVERYTHING that happens, there's going to be a LOT of entries into the log. Usually SQL is much, much faster than Dlookups or most coding solutions. I'll see what happens, though.

Strictly speaking, it's not the Switch() that will kill the performance. It's the multiple LIKE "%<something>%" in a single query that will hurt you badly.

It's possible to optimize a lookup with a LIKE "<something>%" because indices can be used to find all records with the leading prefix. But if you put the wildcards somewhere in middle or at the start of string, as this: LIKE "%<something>" then it's almost likely to be a table scan because there's no other way for JET to find records that could be "%<something>".

Then doing it several times in a single query means multiple passes over the table... so...

I suppose Switch() kind of enabled this by allowing multiple LIKE but it's not as if Switch() is instrinically slow; it's just doing a LIKE "%<something" is expensive and doing it repeatedly is even more so.

I didn't know about those. Nested Iifs were invented by the Devil himself. (At least that's my opinion)

You're in good company here. I've never really liked the idea of nesting Iif() but I guess it's just one of those things that everyone "finds" and use it without abandon because it's good enough.

It's worth a try. At very worst I'll have learned a new SQL command that can come in handy in other situations and I'll add the tag. As I posted to Bob, the tag will only be useful if the program silently enters the tag as part of the SQL statement that adds the whole entry.

Well, to me that's just one more combobox on the entry form, which shouldn't be a big deal for the users- they'll almost certainly appreciate it when they do their reporting knowing it's all categorized.

But if you're bent on automating this (and understand that this is risky because automating too much could occasionally mis-categorize a entry), it should be a simple matter of adding something to the entry form's BeforeUpdate event to identify the category based on the entered description and put it in there automatically. At least have a textbox visible to the user so they can correct it should they not agree with the automatic categorization.

 

[boblarson]

The best way is if the program enters the Type/Category when it enters the log entry. It's just another column in the same query and gets the same results without user interaction!



SHADOW

Not really because it requires quite a bit of coding to create the type/category - and what if the description contains BOTH Complaint and Return - then your code will return the last one in the Switch. Is that what you want - who knows, but the system will do it that way. It might be that there is a word which is in the description which has nothing to do with that it really was a complaint and they included the word return in the description.

So, automated isn't always the answer...

 

[shadow9449]

So, as a test, I ran the query on a table that had over 230,000 entries in the log. The information was retrieved instantly. I feel pretty safe using the Switch approach.

Thanks

SHADOW

 

[ChrisO]

Just because it says so in the help file that doesn’t mean it’s true.

Sub TestIt()

    MsgBox GetType("apples are cheap")
    MsgBox GetType("oranges are cheap")
    MsgBox GetType("bananas are cheap")

End Sub


Function GetType(ByVal strIn As String) As String

    Select Case True
        Case strIn Like "apples*": GetType = "apples"
        Case strIn Like "oranges*": GetType = "oranges"
        Case Else: GetType = "not fruit in list"
    End Select

End Function

 

[Steve R.]

An alternative construct.

DIM IntCase as Integer
IntCase = 0
if like "apples*" then IntCase =1
if like "oranges*" then IntCase =2
if like "something weird" then IntCase =999
Select Case IntCase
Case 0 
       No match Error message
Case 1
       Code for this situation
Case 2
       Code of this situation
Case Else
        A second opportunity for an error message
End Select

 

[ChrisO]

It’s not really an alternative if you are not using ‘Like’ within the Select Case.

 

[shadow9449]

Just because it says so in the help file that doesn’t mean it’s true.

Sub TestIt()

    MsgBox GetType("apples are cheap")
    MsgBox GetType("oranges are cheap")
    MsgBox GetType("bananas are cheap")

End Sub


Function GetType(ByVal strIn As String) As String

    Select Case True
        Case strIn Like "apples*": GetType = "apples"
        Case strIn Like "oranges*": GetType = "oranges"
        Case Else: GetType = "not fruit in list"
    End Select

End Function

That looks like the syntax I was looking for. I don't understand why the compiler accepts your syntax but not the syntax I was using but languages can be really weird...

SHADOW