Search code examples
sqlwhere-clausesql-like

Microsoft Access 2010 LIKE clause character range (e.g., "[a-z]"]) not working

I am developing a custom ODBC driver to which I link using external ODBC data source from Microsoft Access 2010. I issue the following query:

SELECT column_name from table_name where column_name like '[S-T]%';

Access seems to cut off the WHERE clause before submitting it to the ODBC driver manager. When I enable tracing through the ODBC Data Source Administrator, the log shows

SELECT column_name FROM table_name

When I remove the '[S-T]', the query works fine. Also, the same query causing trouble in Access works fine when I run it under ODBC Test (MDAC 2.8 testing tool).

Any idea why Access might be doing this?

Solution

  • So, I asked the same question on the Office Dev Center forum and learned how to do a Pass-Through query to "bypass" Access' attempt to optimize my query. Rather than repeating it all here, here is the link:

    http://social.msdn.microsoft.com/Forums/office/en-US/f105538b-2e6a-42df-baf2-598f85277aba/microsoft-access-2010-cutting-off-where-clause-with-like-st?forum=accessdev