Search code examples
excelms-accessdatasourcesql-like

MS Access LIKE expression has issue when used as MS Excel data-source

I have created an MS Access 2013 query and within the query I have an expression that uses the LIKE operator. I then create an Excel 2013 workbook and select this Access database as a data-source and select the query that contains the LIKE expression. The problem is that it does not seem to like the LIKE expression when returning the data to excel. It shows correct results when running the query from inside Access.

So if I have an expression that looks like this

iif([CAR] like "*A*", True,False)

It will return all CARs in the table and show true or false depending on the string in the CAR field when running the query inside Access. But when setting this query as an Excel data-source, it returns only the results that result in false (no A in the CAR field string)

I know that Excel does not use the LIKE operator, but I thought that the query in Access produces the results and Excel just uses the results.

If you need clarification on the question please ask in the comments.

Solution

  • In Access SQL-89 mode, * is used as a wild card character to match any sequence of zero or more characters. SQL-89 mode is the Access default for the query designer and for queries run from DAO.

    But Access also supports SQL-92 mode which uses % instead of * as the wild card. If you're using ADO to run your query from Excel, it is using SQL-92 mode.

    There are at least 3 things you can do to get the results you expect in Excel:

    1. IIf([CAR] Like "%A%", True,False) - Use this expression in your query to get your expected results in Excel. The downside is you may not see the same results when the query is run in an Access session.

    2. IIf([CAR] ALike "%A%", True,False) - Alike (instead of Like) signals the database engine to expect % instead of * as the wild card, regardless of whether the query is run from SQL-89 or 92 mode. So this expression should give you the same result in both Access and Excel.

    3. IIf(InStr(1, [CAR], "A", 1) > 0, True, False) - InStr doesn't rely on wild card characters so should give you the same results in both Access and Excel.