Why is my LIKE query not returning any records in Microsoft Access 2013 only?

Question

My SQL query is as follows:

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

When I submit this query on W3 School's TryIt Editor (v1.2) (http://www.w3schools.com/sql/trysql.asp?filename=trysql_select_like), it returns a number of records where the SupplierName begins with the letter 'S', as expected.

However, when I run this query on my own database in Access 2013, it doesn't return any records, even though my table name and field name are identical to W3's sample.

Queries not using 'LIKE' seem to work ok, for example:

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

returns the appropriate record(s). It is only when 'LIKE' is used that Access returns no records at all.

My question is, is there any reason why LIKE queries don't return any records? The back-end data source is a Microsoft JET database (this is a relatively small database - no need for full SQL Server), but I don't think this should make a difference. Access doesn't complain about syntax or throw an error when I execute the query.

Chateau

See also: #17000704 – Chateau

This has been answered several times from what I can tell. See https://mcmap.net/q/524780/-why-does-a-like-query-in-access-not-return-any-records for a comprehensive answer. – Coterminous

Answer 1

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

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

The query should be modified like so:

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

Chateau

Why does JET use * instead of % for wildcards in LIKE operations? Conversely, why does standard SQL use the ANSI-standard % wildcard instead of the standard * wildcard? – Chateau

In my opinion, Access should throw some kind of warning if it detects the use of full-SQL syntax in a query of a JET database. – Chateau

I think the question really is why does Access not use the standard % wildcard? It is similar to IE vs Internet Standards. – Fetid

SQL has certainly become the de facto standard for database queries, but what I meant was in my experience (regex, etc.), * is the wildcard character. So, that's why I was asking both- why SQL (which is based on ANSI) deviates from the conventional standard, and why Access deviates from the SQL (ANSI) standard. – Chateau