What is wrong with this expression?

[jason]

When I try to run this I get an error about invalid use of '.', '!', or
'()'.
Not sure where I'm misplacing the ( ).


SELECT PatDataLink.pat_number, PatDataLink.f_name, PatDataLink.l_name,
PatDataLink.terms
FROM PatDataLink
WHERE (((PatDataLink.terms) Like "*OC*") (SELECT PatDatalink.terms FROM
PatDataLink WHERE (((PatDatalink.terms) <> "doctor"))));

 

[Bill Mosca, MS Access MVP]

Jason

Maybe I'm not reading your query right, but why do you have a select
statement in your WHERE clause?

 

[jason]

I am trying to create a subquery. I got the idea from the Help file.
Per the Access Help file:
SELECT * FROM Products

WHERE UnitPrice > ANY

(SELECT UnitPrice FROM OrderDetails

WHERE Discount >= .25);

Basically, I'm looking in the field PatDataLink.terms for "OC" but NOT for
"doctor".
If there's a better way of doing this, please advise.

 

[Douglas J. Steele]

I don't see any reason why you need a subquery:

SELECT PatDataLink.pat_number, PatDataLink.f_name, PatDataLink.l_name,
PatDataLink.terms
FROM PatDataLink
WHERE PatDataLink.terms LIKE "*OC*" AND PatDatalink.terms <> "doctor"

If you did need a subquery, it would need to be something like:

SELECT PatDataLink.pat_number, PatDataLink.f_name, PatDataLink.l_name,
PatDataLink.terms
FROM PatDataLink
WHERE PatDataLink.terms IN (SELECT PatDatalink.terms FROM
PatDataLink WHERE PatDatalink.terms LIKE "*OC*" AND PatDatalink.terms <>
"doctor" );

 

[Bill Mosca, MS Access MVP]

Ah! You misinterpretted the ANY operator. ANY is a way of checking if any
record has a field with a value in it.

I think what you really need is just another criterion.
Try:
SELECT PatDataLink.pat_number, PatDataLink.f_name, PatDataLink.l_name,
PatDataLink.terms
FROM PatDataLink
WHERE PatDataLink.terms Like "*OC*"
AND PatDatalink.terms <>"doctor"


Using a subquery (which you do not have to do in your case) would look like
this:
SELECT PatDataLink.pat_number, PatDataLink.f_name, PatDataLink.l_name,
PatDataLink.terms
FROM PatDataLink
WHERE PatDataLink.terms Like "*OC*"
AND PatDataLink.terms NOT IN(SELECT PatDatalink.terms
FROM PatDataLink
WHERE PatDatalink.terms <> "doctor");

 

[jason]

Thanks Douglas and Bill. But I seem to get "doctor" in my results when I
DON'T want to view these.
I thought that by having a subquery, I could SELECT the records that are not
equal to "doctor". Then from that subquery, query the records that match
"OC".
Seems like it may be more difficult than I thought.

 

[Douglas J. Steele]

What is your query now? And, just to make certain, you say it's returning
rows where PatDatalink.terms is equal to "doctor" (as opposed to
PatDatalink.terms containing "doctor"

 

[jason]

The field PatDataLink.terms is a memo field so "doctor" and "OC" are
contained somewhere in the field.

This is returning rows CONTAINING "doctor" as well as "OC":
SELECT PatDataLink.pat_number, PatDataLink.f_name, PatDataLink.l_name,
PatDataLink.terms
FROM PatDataLink
WHERE PatDataLink.terms Like "*OC*"
AND PatDatalink.terms <>"doctor";

I also used your subquery:
SELECT PatDataLink.pat_number, PatDataLink.f_name, PatDataLink.l_name,
PatDataLink.terms
FROM PatDataLink
WHERE PatDataLink.terms Like "*OC*"
AND PatDataLink.terms NOT IN(SELECT PatDatalink.terms
FROM PatDataLink
WHERE PatDatalink.terms <> "doctor");

But came up blank. I changed the last line to
WHERE PatDatalink.terms = "doctor");
and it shows results, but again, displays "doctor".

 

[David F Cox]

have you tried:

(PatDataLink.terms Like "*OC*") AND (PatDatalink.terms <>"doctor");

 

[jason]

Thanks David.
Still the same results.

have you tried:

(PatDataLink.terms Like "*OC*") AND (PatDatalink.terms <>"doctor");

 

[Bill Mosca, MS Access MVP]

You weren't clear, but I think I understand what you are after now.

Try:
WHERE PatDataLink.terms Like "*OC*"
AND PatDatalink.terms NOT LIKE "*doctor*";

 

[jason]

Sorry I wasn't clear enough. I've tried your suggestion, but still showing
same results. Actually with your last query, it removed 1 record with the
word "doctor" in it, but all the other ones are still there.

I'll just have to go through the query and delete records I don't need.
Painful, but needs to be done.

Really appreciate the input from others on this newsgroup.

 

[Douglas J. Steele]

Yup. For the record, Jason, your subquery didn't work because none of the
records are equal to doctor: they'd only be equal to doctor if that was the
only thing contained in the field.

 

[David F Cox]

I had more faith in Jason than that. I thought he was dealing with a job
title field. I was going to suggest he looked for the almost invisible
leading blank, they keep cropping up.

AND NOT LIKE "*doctor*" would solve it whatever.