Update query using NOT IN

[Lady_Jane]

Hi everyone,
Here is the query I am trying to run:

UPDATE tbl_CatchmentsCSL
SET NoWasteSites = "Yes"
WHERE UID NOT IN (SELECT UID FROM tbl_PointStressorsLKP) AND Study = "SEO"

This is giving me a "must use an updateable query" error. From googling I understand that this is because you cannot update using a subquery, but I do not know a way around it.

Suggestions?


Thank-you!

[June7]

Try DLookup

UPDATE tbl_CatchmentsCSL
SET NoWasteSites = "Yes"
WHERE DLookup("UID","tbl_PointStressorsLKP","UID=" & [UID]) Is Null AND Study = "SEO"

[Lady_Jane]

Thanks,
That's what I was looking for, I just didn't know how to get the DLookup equivalent of NOT IN.
I ran the query, however, and it says "unknown"...?

[Lady_Jane]

Figured it out: there was an issue with the quotes. [UID] is a string, so I converted the Query to:

UPDATE tbl_CatchmentsCSL SET NoWasteSites = "Yes"
WHERE DLookup("[UID]","tbl_PointStressorsLKP","[UID]='" & [UID]&"'") Is Null And Study="SEO";

and it works