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

by

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

More Related Contents:

  1. sql in ms-access [closed]
  2. Managing and debugging SQL queries in MS Access
  3. SQL multiple join statement
  4. How do I write a full outer join query in access
  5. What’s the difference between DoCmd.SetWarnings and CurrentDB.Execute
  6. How to do INSERT into a table records extracted from another table
  7. Multiple INNER JOIN SQL ACCESS
  8. Table-less UNION query in MS Access (Jet/ACE)
  9. SQL Query – Using Order By in UNION
  10. Ms Access Query: Concatenating Rows through a query
  11. Access-SQL: Inner Join with multiple tables
  12. is there a group_concat function in ms-access?
  13. Address standardization within a database
  14. How do I implement pagination in SQL for MS Access?
  15. SQL to add column with default value – Access 2003
  16. Achieving ROW_NUMBER / PARTITION BY in MS Access
  17. SQL Updating from an inner join
  18. How can I implement SQL INTERSECT and MINUS operations in MS Access
  19. Lightweight SQL database which doesn’t require installation [closed]
  20. Convert String to Date in MS Access Query
  21. HOW TO SELECT PREVIOUS ROW VALUE? [duplicate]
  22. MSAccess – query to return result set of earliest rows with a unique combination of 2 columns
  23. Editing Record issues in Access / SQL (Write Conflict)
  24. How do I create a table alias in MySQL
  25. How to save the result of a SQL query into a variable in VBA?
  26. Select query with date condition
  27. Does MS access(2003) have anything comparable to Stored procedure. I want to run a complex query in MS acceess
  28. Microsoft Access – Case Query
  29. CONCAT equivalent in MS Access
  30. Check if access table exists

Leave a Comment