Delete query based on matches in another table

[mssbass]

I want to create a query that deletes any phone numbers that DO NOT match area codes in my area code table. However, it's not working. For example it shows it will delete area code 413 but 413 IS in the area code table!

SQL:

SELECT Left([r00]![phonenum],3) AS Expr1
FROM r00, areacodes
WHERE (((Left([r00]![phonenum],3)) Not Like [areacodes]![areacode]));

 

[BNPMike]

You can't say it like that. It's unusual SQL but I guess SQL will match each phonenum to all the area codes that are not the same as it.

Try a similar version which is NOT IN (select areacodes from areacodes). Remove areacodes from the main query


So roughly:

SELECT Left([r00]![phonenum],3) AS Expr1
FROM r00
WHERE (((Left([r00]![phonenum],3)) Not NOT IN (select areacodes from areacodes));

 

[BNPMike]

Obviously not two nots so more like:

WHERE (((Left([r00]![phonenum],3)) NOT IN (select areacodes from areacodes)
));