Why doesn't my LIKE query return any records in Microsoft Access 2013?

Question

My SQL query is as follows:

SELECT * FROM Suppliers WHERE SupplierName LIKE 's%';

When I submit this request to W3 School TryIt Editor (v1.2) ( http://www.w3schools.com/sql/trysql.asp?filename=trysql_select_like ), it returns a few entries where the Provider Name begins with the letter "S ", as expected.

However, when I run this query in my own database in Access 2013, it does not return any records, although my table name and field name are identical to the W3 sample.

Queries not using "LIKE" seem to work fine, for example:

SELECT * FROM Suppliers WHERE SupplierName="Part Supplier, Inc.";

returns the corresponding record (s). Only when "LIKE" is used, Access does not return any records.

My question is, is there a reason LIKE queries do not return any records? The internal data source is the Microsoft JET database (this is a relatively small database — no need for a full SQL Server), but I don’t think this should affect the situation. Access does not complain about the syntax or throws an error while executing the request.

Answer 1

From http://technet.microsoft.com/en-us/library/cc966377.aspx :

Microsoft Jet uses partial matching characters (or "wildcards") with the Like operator, which are different from those used in most SQL dialects. The asterisk (*) matches zero or more characters and is equivalent to the percent character (%) in ANSI SQL. Other Microsoft Jet partial match characters are the question mark (?), Which matches any character in one field, and the number sign (#), which matches any digit in one field.

The request should be modified as follows:

SELECT * FROM Suppliers WHERE SupplierName LIKE 's*';