Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2009-07-14
6
Medium Priority
?
316 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 2000 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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

886 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