InStr expression as Case in Select not as expected

[GraeagleBill]

I have a new DB where a description field in one of its tables is cluttered with extraneous data that resulted from a migration process. In an attempt to clean things up a bit where there was an abundance of repetition, I created a function to be used in an UPDATE query to replace the description field with only the essential. I monitored the function in Debug but none of the InStr Case statements returned a value greater then zero, whereas an isolated debug statement (not shown here) clearly indicated that the InStr expressions were in fact finding the desired strings as coded.

Is there some special syntax consideration when the InStr function is used to test a "Case"?

Option Compare Database
Option Explicit


Public Function FixDesc(strDes As String) As String


FixDesc = strDes


    Select Case FixDesc
        Case InStr(strDes, "Amazon") > 0
            FixDesc = "Amazon"
        Case InStr(strDes, "AMZN") > 0
            FixDesc = "Amazon"
        Case InStr(strDes, "APPLE.COM/BILL") > 0
            FixDesc = "APPLE.COM/BILL"
        Case InStr(strDes, "HUTCHS MISSION") > 0
            FixDesc = "HUTCHS MISSION"
        Case InStr(strDes, "IN TOUCH") > 0
            FixDesc = "IN TOUCH"
        Case InStr(strDes, "LA CUCINA") > 0
            FixDesc = "LA CUCINA"
        Case InStr(strDes, "METAMORPHOSIS") > 0
            FixDesc = "METAMORPHOSIS"
        Case InStr(strDes, "Netflix") > 0
            FixDesc = "Netflix"
        Case InStr(strDes, "PACIFIC JUSTICE") > 0
            FixDesc = "PACIFIC JUSTICE"
        Case InStr(strDes, "QVC") > 0
            FixDesc = "QVC"
        Case InStr(strDes, "SAVEMART") > 0
            FixDesc = "SAVEMART"
        Case InStr(strDes, "SWANSON") > 0
            FixDesc = "SWANSON"
        Case InStr(strDes, "VILLAGE MISSIONS") > 0
            FixDesc = "VILLAGE MISSIONS"
    End Select
    
End Function

[orange]

Do you have some sample records showing the sort of things in strDes?

[June7]

Your Select Case doesn't make sense unless FixDesc has value of True or False because each of those Case InStr() > 0 expressions will return either True or False. You are looking for substring within strDes so strDes certainly not a True or False. Then you set FixDesc equal to strDes which is not True or False.

So try:

Select Case True
...

[GraeagleBill]

Sure, "SHP*SWANSON HLTH PROD 800-437-4148 ND 3664 7294 685603425201*5OF5"

I expected the statement "Case InStr(strDes, "SWANSON") > 0" to return a value of 5

[Minty]

The case expression doesn't work like that

You are Testing the result of FixDesc which will never equal any of the subsequent case statements
As June suggested you need to use something like

SELECT CASE True
     CASE ....

[GraeagleBill]

PERFECT!

I was, perhaps not obviously, thinking "In the case where InStr(strDes, "SWANSON") > 0" set the return value, but ignoring the object of the Select.

Thanks,
Bill