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"?
Code:
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