Reputation: 531
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
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
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
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