Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2009-07-14
6
Medium Priority
?
314 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 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

715 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