Why does a LIKE query in Access not return any records?

Question

Is there any reason why

SELECT * FROM MyTable WHERE [_Items] LIKE '*SPI*'

does not return any records with OleDbAdapter.Fill(DataSet) or OleDbCommand.ExecuteReader()?

When I run the same SQL in MS Access directly, it returns the expected records. Also, in the same code, if I change the SQL to

 SELECT * FROM MyTable 

all records are returned.

Karrah

What exactly does it mean to run an SQL query "in MS Access directly?" – Arkwright

In MS Access, you can create a query, in SQL mode. That is what I meant to type the query in Access directly. – Karrah

Answer 1

Change your * to % as % is the wildcard search when using OLE DB.

SELECT * FROM MyTable WHERE [_Items] LIKE '%SPI%' 

Conventual

This answer fails to communicate that * is correct in certain situations, though % is correct in other situations. My access 2010 database prefers *. See @onedaywhen's answer below. – Ljubljana

Answer 2

Try changing LIKE to ALIKE and your wildcard characters from * to %.

The Access Database Engine (Jet, ACE, whatever) has two ANSI Query Modes which each use different wildcard characters for LIKE:

• ANSI-89 Query Mode uses *

• ANSI-92 Query Mode uses %

OLE DB always uses ANSI-92 Query Mode. DAO always uses ANSI-89 Query Mode. The Access UI can be set to use one or the other.

However, when using ALIKE keyword the wildcard character is always % regardless of ANSI Query Mode.

Consider a business rule that states a data element must consist of exactly eight numeric characters. Say I implemented the rule as follows:

CREATE TABLE MyStuff 
(
 ID CHAR(8) NOT NULL, 
 CHECK (ID NOT LIKE '%[!0-9]%')
);

It is inevitable that I would use % as the wildcard character because Access's CHAR data type and CHECK constraints can only be created in ANSI-92 Query Mode.

However, someone could access the database using DAO, which always uses ANS-89 Query Mode, and the % character would be considered a literal rather than a 'special' character, and the following code could be executed:

INSERT INTO MyStuff (ID) VALUES ('%[!0-9]%');

the insert would succeed and my data integrity would be shot :(

The same could be said by using LIKE and * in a Validation Rule created in ANSI-89 Query Mode and someone who connects using ADO, which always uses ANSI-92 Query Mode, and INSERTs a * character where a * character ought not to be.

As far as I know, there is no way of mandating which ANSI Query Mode is used to access one's Access database. Therefore, I think that all SQL should be coded to behave consistently regardless of ANSI Query Mode chosen by the user.

Note it is not too difficult to code for both using LIKE with the above example e.g.

CHECK (
       ID NOT LIKE '%[!0-9]%'
       AND ID NOT LIKE '*[!0-9]*'
      )

...or indeed avoid wildcards completely e.g.

CHECK (ID LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')

However, using ALIKE will result in less verbose code i.e. easier for the human reader and therefore easier to maintain.

Also, when the time comes to port to a SQL product that is compliant with SQL Standards, ALIKE ports well too i.e. transforming the ALIKE keyword to LIKE is all that is required. When parsing a given SQL predicate, it is far, far easier to locate the one LIKE keyword in than it is to find all the multiple instances of the * character in text literals. Remember that "portable" does not mean "code will run 'as is'"; rather, it is a measure of how easy it is to move code between platforms (and bear in mind that moving between versions of the same product is a port e.g. Jet 4.0 to ACE is a port because user level security no longer functions, DECIMAL values sort differently, etc).

Reprehend

I assume ALIKE is short for ANSI LIKE. I don't see it as of any use except for people who are foreigners to Access. It means you end up writing non-portable SQL, but really, what matters is your data interface method. If you are a refugee from outside Access and knee-jerk must use % as your wildcard, then all you have to do is insure that you always use data access methods that will use ANSI 92 mode. That will result in portable SQL and with no proprietary kludges like ALIKE. Really, if you're going to use ALIKE, you might as well just use * -- both are non-portable. – Archaism

@David-W-Fenton: "all you have to do is insure that you always use data access methods that will use ANSI 92 mode" -- rather, one would have to ensure that all users only use ANSI-92 Query Mode but how would one do that? As far as I know it is not possible. Therefore, you need to handle both ANSI Query Modes or expose your clients to data integrity issues. Also, ALIKE is highly portable, far more portable than using LIKE + *. I've updated my answer accordingly. – Reprehend

ALIKE is supported by other database engines? I didn't know that -- I thought it was Jet/ACE-specific. Just from Googling for a minute, I find no evidence of ALIKE being supported by any other database engine. – Archaism

@onedaywhen: I don't understand your point about "ensure that all users only use ANSI-92 Query Mode". If you're building an application, you've got full control on that. If you have a Jet/ACE back end that you'd like to restrict to that mode, you set that property for that database. Now, that won't have any effect whatsoever on access to that database from DAO, but it will mean that if somebody opens it in Access, it will use SQL 92 mode. I don't know that ODBC supports it in any scenario, but I could be wrong. So, I don't get your point. – Archaism

@David-W-Fenton: "If you're building an application, you've got full control on that... Now, that won't have any effect whatsoever on access to that database from DAO... I don't know that ODBC supports it..." -- you've kind of made the point for me: you don't have any control over the ANSI Query Mode your SQL code will be executed in! If you think you do then you are kidding yourself :) – Reprehend

@David-W-Fenton: "I find no evidence of ALIKE being supported by any other database engine" -- See my update: "Remember that "portable" does not mean "code will run 'as is'"; rather, it is a measure of how easy it is to move code between platforms..." Transforming ALIKE to LIKE is one of the easiest ports you can hope for :) – Reprehend

You mean it's much easier than transforming SQL with "*" as wildcard to using "%" as wildcard? Looks like a plain search and replace in both cases to me, so I can't see why one would prefer one over the other. – Archaism

@David-W-Fenton: No, I mean it's much easier to transform ALIKE to LIKE than it is to transform * to % -- consider that you may be parsing a concatenated string, handling *, CHR(42) and CHR$(42), and that the * character or 42 ascii code could be stored in a table! But it's a minor issue compared with the ANSI Query Mode neutrality one. – Reprehend

Answer 3

Try converting your wildcard chars (*) to %

This should sort the issue out.

Lychnis

Answer 4

Jeez, this works! Thanks a lot.

I just had to replace not like criteria to not alike criteria.

I'm sharing my "story" to help others find this post easier and save them from a two hours search.

Although I've linked the Excel 95-97 xls files to the Access 2010 database, and ran create table and insert into queries to import all data into a database, for some strange reason, the select query couldn't find the strings I've typed.

I tried not like "something" and not like "%something%" with no success - simply didn't work.

L

Vevay