SQL Union query that produces Data Type Mismatch error

Question

I created the following query in Access 2007-2010

SELECT [Zip Code Count].[Zip Code], [Zip Code Count].CountOfAddress1
FROM [Zip Code Count]
WHERE [Zip Code Count].[Zip Code] NOT IN (Select [Zip Code List].[Zip Code] From [Zip Code List])

UNION  

Select [Covered Zips Count].[Zip Code], [Covered Zips Count].CountOfAddress1
FROM [Covered Zips Count]
Where [Covered Zips Count].CountOfAddress1 < 10;

The Covered Zip Counts and Zip Code Count are both aggregate queries from a larger (800+ record) set of data. Zip Code List is a simple table of specific zip codes.

What I am trying to do is to make a list of those zip codes that are not in the Zip Code List plus those zip codes which are in the list but the total count of records is less than 10 (this is necessary for bulk mailing via the postal service).

I get the following error.

Data type mismatch in criteria expression

Both query parts on their own work perfectly fine. The output of both queries is exactly the same: the Zip codes are text and the Counts are integers. This query seems like it should just work but it refuses to.

What are the datatypes of columns [Zip Code Count].[Zip Code] and [Covered Zips Count].[Zip Code] ? They need to be the same. Also, [Zip Code Count].CountOfAddress1 and [Covered Zips Count].CountOfAddress1 need to have the same datatype. — GMB

glen.hinckley glen.hinckley · Accepted Answer · 2019-02-24T22:38:17

I think I solved the problem...well sort of. I attempted to make separate queries and then union them in another query. That gave me the same error, so I tried to remove one of the fields. Then I got the error "Query is too complex". So I am guessing that the software couldn't handle it.

I ended up turning the original aggregation query of zip codes into a Make Table query. Once I had a solid set of data, the UNION query I tried in the first place worked perfectly.

SQL Union query that produces Data Type Mismatch error

1 Answers