ODBC/Access LIKE fails for subquery

[Law]

I'm using ODBC to perfrom SQL on a MS-Access database
(Access 2000), and have encountered a problem with
subqueries using a LIKE clause.

Here's the SQL...

SELECT *
FROM TableA
WHERE id IN
(
SELECT id
FROM TableA
WHERE name LIKE 'O%'
)

If I change the % to a * then the query works but then the
simpler case of running the simple query like this fails.
SELECT id
FROM TableA
WHERE name LIKE 'O*'

Running the queries directly in Access work with the *
notation but not the % notation. What is the ODBC Jet
driver doing (its version 4.0 supposedly ANSI SQL-92
compliant so should support the % in all cases)

Anyone tell me why and what a possible work around is, and
is it a known problem?

 

[Jen]

Hi,

Instead of the SQL standard %, try using a ?. From what
I've read and understand, Jet 4.0 has been trying to come
up to SQL standards, but isn't quite there yet.

Hope that helps.

Jen