If you want to join two tables in MS Access using the LIKE operator you will fail receiving an error message stating the following.

"JOIN expression not supported."

MS Access SQL

The SQL producing above error looks as follows.

1
2
SELECT *
FROM RULE LEFT JOIN ENTALL ON CAS1 LIKE ('*' & RULE.[SOD CAT CLEAN] & '*');

 You can fix this by changing the SQL to define the JOIN condition in the WHERE part of a statement.

1
2
3
4
5
SELECT * 
FROM RULE, ENTALL 
WHERE CAS1 IS NOT NULL 
AND RULE.[SOD CAT CLEAN] IS NOT NULL 
AND ENTALL.CAS1 LIKE ('*' & RULE.[SOD CAT CLEAN] & '*');

You have to be careful with these queries and make sure that you exclude NULL explicitly from this type of JOIN.

If you do not explicitly exclude NULL, the query will produce invalid results. I check both join fields for NULL before I implement the actual JOIN condition, hoping that Access will exclude NULL from a result set first and then attempts to join.

The performance of this query is expected to be bad since the "database" has to apply a full like search on all records (full table scan).