Solved

Creating an unmatched query using two queries with formatted data

Posted on 2009-04-14
3
458 Views
Last Modified: 2013-11-27
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

0
Comment
Question by:jdol2949
  • 2
3 Comments
 

Author Comment

by:jdol2949
ID: 24142189
To make things a little easier.. I'm going to post the test DB that I've been working on.
test.mdb
0
 
LVL 26

Accepted Solution

by:
Chris Luttrell earned 500 total points
ID: 24144564
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
 

Author Closing Comment

by:jdol2949
ID: 31570124
Exactly what I was looking for.  Thanks CGLuttrell!!
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now