help with subqueries and wildcards

[Eric]

Hi all,
I am having trouble with using wildcards within a subquery. More
specifically, I'm trying to pull information on certain model machines
from my main table, while excluding models that I have entered into a
separate table.

so my query looks like this thus far:

SELECT all_machines.*
FROM all_machines WHERE (all_machines.Model <> ANY (SELECT
excluded_models.excluded FROM excluded_models))

the problem that I am having is that the query correctly excludes all
the models where I have explicitly written the complete model number
(ex. ICOPY500) but it does not exclude the models where I have written
them using wildcards (ex. ECOPY*). I believe that the query ends up
looking for the exact text ICOPY* rather than recognizing the * as a
wildcard, but I don't know how to fix it. I have tried to insert a LIKE
function, but i keep getting errors. Any ideas?

Thanks in advance

Sincerely,
Eric

 

[Michel Walsh]

Hi,


Indeed, you will need the operator like, but like does NOT support universal
quantifier (ALL, SOME, ANY):


SELECT*
FROM machines
WHERE NOT Model LIKE ANY (SELECT excluded FROM excluded_models)



so, the previous statement won't work, but you can use EXISTS:



SELECT *
FROM machines
WHERE NOT EXISTS( SELECT * FROM excluded_models WHERE machine LIKE excluded)



Hoping it may help,
Vanderghast, Access MVP