Access 2010 query is case-sensitive

Question

In Microsoft Access 2010 on Windows 7, my query against a linked ODBC table is case-sensitive. How can I disable that?

Specimen_Type_Description Like "*rect*"

in the criteria excludes "Rectal biopsy" but includes "Stomach (gastrectomy)".

I know I could use LCase() or UCase(), but I get the impression that Access is normally case-insensitive, so I want to know how to make it so, and why it isn't in this case.

Answer 1

Thanks but under SQL Server Compatible Syntax (ANSI 92)

- This database

• Default for new databases

are both already unchecked. I ran the same query on an imported local copy of the same table and it returned all the desired results, so clearly the query is only case-sensitive when run against

the linked ODBC table. It is an InterSystems Caché table; its driver is "InterSystems ODBC35".

I tried checking "SQL Server Compatible Syntax (ANSI 92)" for "This database" and the query returns the no results from local or linked table, with or without the field converted with LCase() before comparison.

Answer 2

It sounds like you have the ANSI SQL option turned on. Go to File tab>Options>object designers. Under the query area, make sure the ANSI SQL is unchecked.