CODEWITHSUNDEEP
sqlms-access
Chuck0185
Chuck0185

Reputation: 531

Joining on Is Null MS Access SQL

How do I join 2 tables in MS Access that may have NULL values in the join fields? For instance I have 2 tables, Table1 and Table2. I would like to join them on Table1.[Test Field 1] = Table2.[Test Field 1]. Also if the field in both tables is NULL I would like them to join. I have tried a couple different ways of going about this, including the SQL below. When I try to run that I get the error message: "Missing ),], or Item in Query expression..." I've tried inserting more brackets etc but nothing has worked. 

SELECT Table1.[Test Field 1], Table1.[Test Field 2], Table2.[Test Field 1], Table2.[Test Field 2]
FROM Table1 INNER JOIN Table2 ON Table1.[Test Field 1] = Table2.[Test Field 1] 
OR (ISNULL(table1.[test field 1]) AND ISNULL(table2.[Test Field 1]));

Upvotes: 2

Views: 4960

Answers (3)

Siegmund
Siegmund

Reputation: 1

Another way is using the Nz-Function in the join. Nz substitutes NULL with the second item

JOIN 
ON    …
AND  (Nz([Tab1].[MayBeNULL_Field],"SubstitutingValue") 
   =  Nz([Tab2].[MayBeNULL_Field],"SubstitutingValue")
     )

Upvotes: 0

danjuggler
danjuggler

Reputation: 1320

IS NULL with a space checks if a column is actually null. This may be what you're looking to use. Here is your query using IS NULL after the column names:

SELECT Table1.[Test Field 1], Table1.[Test Field 2], Table2.[Test Field 1], Table2.[Test Field 2]
FROM Table1
INNER JOIN Table2 ON (Table1.[Test Field 1] = Table2.[Test Field 1] 
   OR (table1.[test field 1] IS NULL AND table2.[Test Field 1] IS NULL));

Upvotes: 0

Erik A
Erik A

Reputation: 32682

Any ON clause in Access with more than 1 comparison needs to be wrapped in parentheses. Unfortunately, the error message you got is not very descriptive, but it's easy to fix.

SELECT Table1.[Test Field 1], Table1.[Test Field 2], Table2.[Test Field 1], Table2.[Test Field 2]
FROM Table1 INNER JOIN Table2 ON (Table1.[Test Field 1] = Table2.[Test Field 1] 
OR (ISNULL(table1.[test field 1]) AND ISNULL(table2.[Test Field 1])));

Upvotes: 3

Related Questions