Solved

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

Posted on 2009-07-14
6
312 Views
Last Modified: 2013-11-27
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?
0
Comment
Question by:travisjbennett
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 1

Author Comment

by:travisjbennett
ID: 24850794
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
 
LVL 1

Author Comment

by:travisjbennett
ID: 24850833
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
 
LVL 11

Accepted Solution

by:
datAdrenaline earned 500 total points
ID: 24851545
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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 3

Expert Comment

by:BrightApollo
ID: 24852031
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
 
LVL 1

Author Comment

by:travisjbennett
ID: 24852614
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
 
LVL 11

Expert Comment

by:datAdrenaline
ID: 24856519
You're welcome, I am glad to have been able to help out!
0

Featured Post

[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question