Creating an unmatched query using two queries with formatted data

Using the code snippet below, I've tried to make an unmatched query using the query wizard to show all records in qrySample where no records from qryDNCNumbers match any fields in qrySample..  DNC meaning 'Do not call'.  These two queries are just simply reformatted phone lists that I have stored in two tables.  qrySample has an expression that formats all the phone numbers in tblSample to make all of the phone numbers appear in a certain format (Ex: (123) 456-7890), and qryDNCNumbers does the same thing for tblDNC.  Reason being, both of these tables that have phone numbers may be formatted (651) 123-2456, 651-123-2456, or 651 123 2456, and other formats.  So, I formatted the two tables to share a universal format to match the data.  I've already created a query to show data that matches from the two formatted queries, but I can't seem to get a query that shows all data from qrySample except for where qrySample.Sample1 = qryDNCNumbers.DNC1.  Access keeps giving me the "data type mismatch" error, even though both columns from each table are type 'text'.  What do I need to do to get the Sample list to not include numbers from the formatted DNC query?
Thanks for your help ahead of time.
SELECT qrySample.Sample1
FROM qrySample LEFT JOIN qryDNCNumbers ON qrySample.Sample1 = qryDNCNumbers.DNC1
WHERE (((qryDNCNumbers.DNC1) Is Null));

Open in new window

jdol2949Asked:
Who is Participating?
 
Chris LuttrellConnect With a Mentor Senior Database ArchitectCommented:
I got the following query to run against your db and it excluded the (651) 452-5630 number that is in both tables.  Will this work for you.
SELECT Sample1
FROM qrySample
where Sample1 not in
(select DNC1
from qryDNCNumbers);

Open in new window

0
 
jdol2949Author Commented:
To make things a little easier.. I'm going to post the test DB that I've been working on.
test.mdb
0
 
jdol2949Author Commented:
Exactly what I was looking for.  Thanks CGLuttrell!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.