Comparing fields to find none matches

Question

SELECT Left([PTC Import].[ZipCode],5) AS Expr1, [PTC Import].[Country], [ZipCodeDatabase_STANDARD].[ZipCode]
FROM [PTC Import] 
LEFT JOIN [ZipCodeDatabase_STANDARD].[ZipCode] ON Expr1 = [ZipCodeDatabase_STANDARD].[ZipCode]
WHERE ((([ZipCodeDatabase_STANDARD].[ZipCode]) Is Null) AND (([PTC Import].[Country])="USA"
FROM [PTC Import], ZipCodeDatabase_STANDARD;

Ok what I have here is my attempt to compare the ZipCode field from my import to our ZipCode database to validate and make sure it is in fact a valid zip code. I would like this query to display any records that do not match a valid zip code where the country is USA.

I am currently getting a syntax error in JOIN statement error message.

I have the ZipCode db linked from another Access db if that would affect anything.

Answer 1

The last line

FROM [PTC Import], ZipCodeDatabase_STANDARD;

shouldn't be there. I think you had an inner join and forgot to remove it:

Then you have an error in your LEFT JOIN with [ZipCodeDatabase_STANDARD], where you are using an alias you defined in FROM. You should use Left(i.[ZipCode],5), since the alias isn't defined yet.

Additionally, i added an alias for each table to make it easier to read.

It should be:

SELECT Left(i.[ZipCode],5) AS Expr1, 
       i.[Country], 
       zs.[ZipCode]
FROM [PTC Import] i 
LEFT JOIN [ZipCodeDatabase_STANDARD] zs ON Left(i.[ZipCode],5) = zs.[ZipCode]
WHERE zs.[ZipCode] Is Null AND i.[Country])="USA";