Using Like in Access SQL

try this:

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

Select allOpen in new window

You need to wrap the 'LIKE' part in wild cards.

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

OM Gang

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

A day late and a dollar short..........................

OK ..

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

mx

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

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?

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

OM Gang

Try this:

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

mx

My last post was for use in VBA code

The issue with the Like operator is always going to be special characters.  With  InStr(), you do not have that problem.

mx

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;"

Did you try the InStr approach ?

mx

Not sure how the Instr() would go into my sql... help?

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

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

OM Gang

*********   InStr requires no asterisks or quotes

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;

As as test, what happens if you remove the WHERE clause ?

mx

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;

"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

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).

"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

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.

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

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).

I see.

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

mx

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

Select allOpen in new window

Thanks everyone for your help... sadly, it was one thing... changing the .Recordset to .Recordsource and like it should, it worked.  Thanks!

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)

Leigh ... more fun >>

https://www.experts-exchange.com/questions/23127516/Are-there-limitations-on-MS-Access-2003-transaction-isolation-levels.html

mx

<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>