Solved

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

Posted on 2009-07-14
6
300 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
  • 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 Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

706 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now