Problems with Co-related subqueries that contain a LIKE clause 2

[nabs]

We are in process of testing an application originally developed in ADO/Access 97 in ACCESS2000. I noticed the following behaviour on the following query :
UPDATE <table1> A
SET A.<COL1> = <some_value>
where
<table1>.Key = ( select <table2>.key
from <table2>
where <table1>.key = <table2>.key
and <table2>.description like 'ABC%' )

This query works fine with Access97/ADO 2.1, but fails to update any rows in ACCESS2000.

Strangely, if I modify the like clause in the above query to read &quot; LIKE 'ABC*' &quot; it works fine in ACCESS2000.
I am at a bit of a loss on this one - but I am sure someone must have encountered this before me if I am not missing something obvious.

Will appreciate any help

 

[Shep]

nabs,
The wildcard character in Access (all versions) is the asterisk (*)
The wildcard character in SQL Server is percentage (%).
I reckon your problem lies there rather than correlated queries. To prove it's nowt to do with correlated queries just try running the subselect in isolation.

Shep

 

[Deadline]

Hi
Does that mean, Access isn't ANSI SQL compliant ?

I have the same problem.


Thank you.

RR

 

[Deadline]

Check this article. It is highly relevant to this topic.

http://www.devguru.com/features/knowledge_base/A100219.html

Hope that helped.!!!


RR