SQL SIMILAR (?): To combine IN and LIKE in Access 2007 VBA

I would like to combine the in and like predicates in Access 2007 into a SHORT string. If it helps, the wildcards will always be on the ends ([S Comment] should include any of the search words.

I tried ([S Comment] LIKE ("*Good*", "*Great*")) but Like won't work like In does (it spews on the commas).

([S Comment] IN ("*Good*", "*Great*")) won't work either: no results found, because IN isn't built to take wildcards.

Normally my list of search words is much longer.

The question: Would Similar work, and what would the syntax look like?
LVL 1
travisjbennettAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

travisjbennettAuthor Commented:
The only example I have is from SQL for Dummies.
Similar: SQL:1999 added the SIMILAR predicate, which offers a more powerful way of finding partial matches than the LIKE predicate provides. With the SIMILAR predicate, you can compare a character string to a regular expression. For example, say you're searching the OperatingSystem column of a software compatibility table to look for Microsoft Windows compatability. You could construct a WHERE clause such as the following:

WHERE OperatingSystem SIMILAR TO
'('Windows '(3.1|95|98|ME|CE|NT|2000|XP))'

Open in new window

0
travisjbennettAuthor Commented:
Unfortunealty, the quotes and array (?) confuse me. I'm not used to this kind of syntax, as I've been using VBA recently.
Can I just do ... (below) ... ?

WHERE [S Comment] SIMILAR TO
'('*'(Good|Great|Fantastic)'*')'

Open in new window

0
datAdrenalineCommented:
SIMILAR TO is not a valid operator in JET/ACE SQL language.  To have multiple LIKE's you will simply has to OR them together .... or create a user defined function ...

Public Function SimilarTo(strBase As String, ParamArray aKeys()) As Boolean
   
    Dim x As Long
   
    If IsArray(aKeys()) Then
        For x = LBound(aKeys) To UBound(aKeys)
            If strBase Like "*" & aKeys(x) & "*" Then
                SimilarTo = True
                Exit For
            End If
        Next x
    End If
   
End Function


Then your query could be ...

SELECT * FROM sometable WHERE SimilarTo([S Comment],"Good","Great") = True

...................

There are other options that I have employed to do keyword searches, but I have had great success with this method.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

BrightApolloCommented:
Working within your db, use this model:

SELECT * FROM tbl
WHERE ([S Comment] LIKE '*Good*' OR [S Comment] LIKE '*Great*')

You'll just be string up your comparisons with OR.  It'll get the job done.
0
travisjbennettAuthor Commented:
I'm trying to keep the SQL string short, and the user defined function looks great. I'll take it.
Until then, I've been using BrightApollo's method, but it overflows eventually.
Thank you both!
0
datAdrenalineCommented:
You're welcome, I am glad to have been able to help out!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.