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.

Answer 1

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

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

Answer 2

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

Answer 3

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

Answer 4

Try converting your wildcard chars (*) to %

This should sort the issue out.