Using Like in Access SQL

I have a query:

SELECT TableA.TableName, TableA.FieldName FROM TableA  WHERE (((TableA.TableName) Like [Forms]![frmSearch]![txtTables].[value]));

How do I allow the user to enter a part of name (like ble X) and the return results would find anything with the "ble X" in the TableName column?

Thanks!
RenitlahHelpAsked:
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.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
try this:
SELECT TableA.TableName, TableA.FieldName FROM TableA  WHERE (((TableA.TableName) Like "*" & [Forms]![frmSearch]![txtTables].[value] & "*" ));

Open in new window

0
omgangIT ManagerCommented:
You need to wrap the 'LIKE' part in wild cards.

WHERE (((TableA.TableName) Like "*" & [Forms]![frmSearch]![txtTables].[value] & "*"));

OM Gang
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
SELECT TableA.TableName, TableA.FieldName FROM TableA  WHERE (((TableA.TableName) Like "*" & [Forms]![frmSearch]![txtTables].[value] & "*" ))
0
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!

omgangIT ManagerCommented:
A day late and a dollar short..........................
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
OK ..

SELECT TableA.TableName, TableA.FieldName FROM TableA  WHERE Instr(1, TableA.TableName , [Forms]![frmSearch]![txtTables]) > 0

mx
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Using InStr will insure that you pick up special characters like quotes, asterisks etc ... wherein Like does not because they can be wild cards.

mx
0
RenitlahHelpAuthor Commented:
This works in a query as you setup...

SELECT TableA.TableName, TableA.FieldName FROM TableA  WHERE (((TableA.TableName) Like "*" & [Forms]![frmSearch]![txtTables].[value] & "*" ))

...but in the VBA window, it fails... I think it may have something to do with the double quotes?
0
omgangIT ManagerCommented:
SELECT TableA.TableName, TableA.FieldName FROM TableA  WHERE (((TableA.TableName) Like '*" & [Forms]![frmSearch]![txtTables].[value] & "*' ));"

OM Gang
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Try this:

SELECT TableA.TableName, TableA.FieldName FROM TableA  WHERE Instr(1, TableA.TableName , [Forms]![frmSearch]![txtTables]) > 0

mx
0
omgangIT ManagerCommented:
My last post was for use in VBA code
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
The issue with the Like operator is always going to be special characters.  With  InStr(), you do not have that problem.

mx
0
RenitlahHelpAuthor Commented:
Ok, here's the SQL I set the .Recordset property to in the VBA, but it kicks back a "Operation not supported by this object" - Error #3251.... Ideas?

"SELECT dbo_MD_SYS_ATTR_DICTIONARY.business_object AS [Table Name], dbo_MD_SYS_ATTR_DICTIONARY.field_name AS [Field Name], dbo_MD_SYS_ATTR_DICTIONARY.default_description AS [Field Description] FROM dbo_MD_SYS_ATTR_DICTIONARY LEFT JOIN dbo_MD_SYS_LOOKUP_ATTR_TYPE ON dbo_MD_SYS_ATTR_DICTIONARY.attribute_type = dbo_MD_SYS_LOOKUP_ATTR_TYPE.attr_type WHERE (((dbo_MD_SYS_ATTR_DICTIONARY.default_description) Like ' * " & [Forms]![frmSearch]![cboDescription].[Value] & " * ')) ORDER BY dbo_MD_SYS_ATTR_DICTIONARY.business_object, dbo_MD_SYS_ATTR_DICTIONARY.field_name;"
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Did you try the InStr approach ?

mx
0
RenitlahHelpAuthor Commented:
Not sure how the Instr() would go into my sql... help?
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:

"SELECT dbo_MD_SYS_ATTR_DICTIONARY.business_object AS [Table Name], dbo_MD_SYS_ATTR_DICTIONARY.field_name AS [Field Name], dbo_MD_SYS_ATTR_DICTIONARY.default_description AS [Field Description] FROM dbo_MD_SYS_ATTR_DICTIONARY LEFT JOIN dbo_MD_SYS_LOOKUP_ATTR_TYPE ON dbo_MD_SYS_ATTR_DICTIONARY.attribute_type = dbo_MD_SYS_LOOKUP_ATTR_TYPE.attr_type WHERE   InStr(1, dbo_MD_SYS_ATTR_DICTIONARY.default_description , [Forms]![frmSearch]![cboDescription] ) > 0
ORDER BY dbo_MD_SYS_ATTR_DICTIONARY.business_object, dbo_MD_SYS_ATTR_DICTIONARY.field_name;"
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
omgangIT ManagerCommented:
Like ' * " & [Forms]![frmSearch]![cboDescription].[Value] & " * '

Like '*" & [Forms]![frmSearch]![cboDescription].[Value] & "*'

OM Gang
0
omgangIT ManagerCommented:
If you're using it on the same form then you can replace
[Forms]![frmSearch]![cboDescription].[Value]
with
Me.cboDescription

OM Gang
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>in vba, it fails

with an error, or just no data returned?
in the first case, start by posting the error
in the second case, try to use % instead of *
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
*********   InStr requires no asterisks or quotes
0
RenitlahHelpAuthor Commented:
Hmmm... still same error, maybe it has to do with the first part before I set the following:

SELECT dbo_MD_SYS_ATTR_DICTIONARY.business_object AS [Table Name], dbo_MD_SYS_ATTR_DICTIONARY.field_name AS [Field Name], dbo_MD_SYS_ATTR_DICTIONARY.default_description AS [Field Description]
FROM dbo_MD_SYS_ATTR_DICTIONARY LEFT JOIN dbo_MD_SYS_LOOKUP_ATTR_TYPE ON dbo_MD_SYS_ATTR_DICTIONARY.attribute_type = dbo_MD_SYS_LOOKUP_ATTR_TYPE.attr_type
WHERE (((InStr(1,[dbo_MD_SYS_ATTR_DICTIONARY].[default_description],[Forms]![frmSearch]![cboDescription]))>0))
ORDER BY dbo_MD_SYS_ATTR_DICTIONARY.business_object, dbo_MD_SYS_ATTR_DICTIONARY.field_name;
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
As as test, what happens if you remove the WHERE clause ?

mx
0
Mike EghtebasDatabase and Application DeveloperCommented:
try:

SELECT D.business_object AS [Table Name], D.field_name AS [Field Name], D.default_description AS [Field Description] FROM dbo_MD_SYS_ATTR_DICTIONARY D LEFT JOIN dbo_MD_SYS_LOOKUP_ATTR_TYPE M ON D.attribute_type = M.attr_type WHERE InStr(D.[default_description],[Forms]![frmSearch]![cboDescription])>0 ORDER BY D.business_object, D.field_name;
0
Leigh PurvisDatabase DeveloperCommented:
Hi all. :-)

Just to touch on a few points.
First of all you don't detail your full actual code anywhere - but you do mention that you assign the ".Recordset property" and get the error "Operation not supported by this object".
What object are you assigning this SQL statement to?
The Recordset property wouldn't be the correct property though (you need to open a recordset and assign the actual object).
The RecordSource of Forms and Reports can be assigned as a SQL string.  But as I say - we'll need to see your code.

Then your issue with performing a wildcard search.
Personally - I like to leave the criteria control for the user to enter wildcards if they so choose (and perform an exact match otherwise).   So perhaps just be sure you want to consider a wildcard search every time.
(For example say the user entered Smith - they would be unable to *not* have returned entries such as Smithson, Smithy, Blacksmith etc etc).

MX mentioned early on that you can use Instr to avoid special character complications.
That's true.
Personally I'd recommend just santising your criteria text before creating the SQL string.
For example, doubling up the quote marks
"... WHERE default_description Like '*" & Replace(Forms!frmSearch!cboDescription,"'", "''") & "*'"
You could strip out asterisks similarly - but as I say, they might actually be deliberate!

Why bother?
Well - with Jet as the BE, you have the potential for much better performance using Like over a function call like InStr.  The function itself has an inevitable performance hit - but also (depending on wildcard use of course) you could also be losing out on the database engine making efficient index use.
e.g. default_description Like 'Smith*'
has the potential to greatly outperform the equivalent query using InStr (with an index on default_description).

That said - the table names you're using clearly look like they're linked SQL Server tables.
In which case Jet optimisation is a factor - but not our whole consideration.
We need to think about what'd being requested from the server.
An old rule of thumb goes vaguely like:
"Linked tables are *not* the root of all evil... but don't make filtered requests which can't be parsed into dialct specific SQL by the ODBC driver"
In other words - using a function call in the Where condition of your SQL statement could very well be responsible for poor query performance whereby all records are returned over the wire and then only filtered locally by Jet.
The goal is always to let the server do the heavy lifting (it's *very* good at it).
Obviously - this is ideally achieved by executing the SQL on the server directly - but when using linked table you need to give Jet every chance of making efficient requests.

That's about all really.
Just a reminder to post that full code yeah?
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
"Well - with Jet as the BE, you have the potential for much better performance using Like over a function call like InStr."

Why is that?

mx
0
Leigh PurvisDatabase DeveloperCommented:
Just as I said before really.
The function call alone adds overhead - though relatively subtle (maybe 10% difference).
But add an index to the mix and the difference can be pronounced.  

Of course though - as I mentioned, index use depends on wildcard use. :-)
i.e. Like default_description Like '*Smith*'
won't get you anything like the performance advantage of
Like default_description Like 'Smith*'
(Though the engine is more efficient with fewer wildcards included anyway - the index can make a stark contrast).
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
"The function call alone adds overhead "

ok ... I can see where string manipulation requires more processing.  But, internally, what is 'Like' doing, ie how is it processed?

mx
0
Leigh PurvisDatabase DeveloperCommented:
The internals are known only to MS - but it's suffice for us to just know that Like performs an expression comparison (not dissimilar to regular expressions in some ways) at the database level (i.e. there's no external function required).
Obviously with such expression comparisons, the simpler the expressions - the easier (and hence quicker) the comparison.
Contrary to common belief though, Like *can* make use of an index.  So if present (and suitably formatted) the comparison will be carried out upon the index rather than the table field.  Giving us that faster lookup.

The only other (possibly) interesting thing I have to say on the subject is that Like cannot make use of an index at all on Binary fields in Jet.  Either they just don't support it (case dinstinction is likely much more effort for Jet) - or it just hasn't been implemented for some reason.
But (short) Binary fields are used so infrequently in Jet that it's rarely a concern.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
So ... If we have a string such as:


Leigh "The Jet*!" Purvis as the value in a Text field in a table or in a text box on a form.  And as you see, the string has double quotes and an asterisk and bang symbol in the middle - ie, one or more wildcard characters (not sure ! is ? - but pretend it is). Lets call that string above 'that string'.

Then, you really can't use Like to say determine if the string contains an asterisk or a sub string which contains some characters which include a asterisk ...

wherein

InStr(1, <that string>, "*") >0  Or InStr(1, <that string>, "Jet*")   will return a value > 0 ... because InStr() will find that asterisk because it doesn't care about wild card characters

Right ?

Meanwhile ... I'm digesting the function returning multiple values thread w/ gin & tonic :-)

mx



0
Leigh PurvisDatabase DeveloperCommented:
If you want to find a string which contains a specific character, say an asterisk, then you can simply compare
WHERE FieldName Like "*[*]*"
using square brackets to begin a character class so as to escape the character's special meaning.

So to find fields which consisted only of a single asterisk character could be
WHERE FieldName Like "[*]"
although obviously this could just be
WHERE FieldName ="*"
But this is just as an illustration. :-)

To find records containing both wildcards (asterisk and question mark)
WHERE FieldName Like "*[*]*[?]*" OR FieldName Like "*[?]*[*]*"

To find records where either are contained
WHERE FieldName Like "*[*?]*"

And so on...
(Worth mentioning though that in general querying the database engine will likely fail to optimise criteria which use OR).
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
I see.

Well, if nothing else, I gues InStr() is a little easier to 'read', LOL.

mx
0
RenitlahHelpAuthor Commented:
Yes, I am using attached SQL Server tables, and it is just a small app to search a few columns.  I have a txtbox and a button to the right of the txtbox which should populate the subform with the data...

See code snipet.  Note this SQL does not return any records in a query and a same error like above when used as a SQL like in my code below.
Private Sub btnSearchDescription_Click()
 
Me.frmResults.Form.Recordset = "SELECT dbo_MD_SYS_ATTR_DICTIONARY.business_object AS [Table Name], dbo_MD_SYS_ATTR_DICTIONARY.field_name AS [Field Name], dbo_MD_SYS_ATTR_DICTIONARY.default_description AS [Field Description] FROM dbo_MD_SYS_ATTR_DICTIONARY LEFT JOIN dbo_MD_SYS_LOOKUP_ATTR_TYPE ON dbo_MD_SYS_ATTR_DICTIONARY.attribute_type = dbo_MD_SYS_LOOKUP_ATTR_TYPE.attr_type WHERE (((InStr(1, [dbo_MD_SYS_ATTR_DICTIONARY].[default_description], [Forms]![frmSearch]![cboDescription])) > 0)) ORDER BY dbo_MD_SYS_ATTR_DICTIONARY.business_object, dbo_MD_SYS_ATTR_DICTIONARY.field_name;"
Forms!frmSearch.Refresh
 
End Sub

Open in new window

0
RenitlahHelpAuthor Commented:
Thanks everyone for your help... sadly, it was one thing... changing the .Recordset to .Recordsource and like it should, it worked.  Thanks!
0
Leigh PurvisDatabase DeveloperCommented:
Yes - that was the exact issue to which I was alluding.  (That you can't assign the Recordset property as a string.)
I see you've noted that you've implemented:

Me.frmResults.Form.Recordsource = "SELECT dbo_MD_SYS_ATTR_DICTIONARY ..." etc

or as a quick example of executing the alternative

Dim strSQL As String
strSQL  = "SELECT dbo_MD_SYS_ATTR_DICTIONARY ..." etc
Set Me.frmResults.Form.Recordset = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
0
Leigh PurvisDatabase DeveloperCommented:
<shrug>
By the time I've gotten there the questioner seems already satisfied in some way.
Not even asked a question about their implementation yet...
No indication of resolution...
</shrug>
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.