join fields containing null values

[John B via AccessMonster.com]

In a query, I have two tables that I'm joining together with Left-Join on two
fields Here's a non-artist's rendering of the two tables

Table1
ItemType
ItemSubType

Table2
ItemType
ItemSubType
ItemDescription

It's a valid situation that the SubType in Table1 and Table2 could be null.
When that happens, I still want to pick up the ItemDescription from Table2
but I get blanks instead. I get all the records I'd expect to get but not
the ItemDescription.

For example, when joining the following records, I will not get "Apple" in
the ItemDescription of my query result for ItemType "A". It will be blanks.

Table1
ItemType = A
ItemSubType = null

Table2
ItemType = A
ItemSubType = null
ItemDescription = Apple

It acts like it won't recognize a valid join if both the join fields contain
null. If there are non-null values in the ItemType and ItemSubType fields it
works fine. Am I missing something?

Thanks in advance for looking!

 

[Chaim]

Try wrapping the ItemSubtype in Nz(). For example, Nz(Table1.ItemSubType,
"Empty"). If the field is null, the string "Empty" will be used instead. As
long as you use the same expression on both fields, the join should work.

Good Luck!

 

[John B via AccessMonster.com]

Hi Chaim,

Sounds like a unique solution! I haven't been able to get this to work so I
have a follow-up question.
Am I supposed to put the Nz wrapper around the join fields in the SQL code
where the Joins are occuring (after the LEFT JOIN Table1 ON.... part of the
statement)? I'm getting a syntax error and haven't been able to get beyond
that.

Thanks...John

Try wrapping the ItemSubtype in Nz(). For example, Nz(Table1.ItemSubType,
"Empty"). If the field is null, the string "Empty" will be used instead. As
long as you use the same expression on both fields, the join should work.

Good Luck!
--

Chaim

In a query, I have two tables that I'm joining together with Left-Join on two
fields Here's a non-artist's rendering of the two tables

[quoted text clipped - 30 lines]

Thanks in advance for looking!

 

[peregenem]

Sounds like a unique solution! I haven't been able to get this to work

SELECT *
FROM Table1
LEFT JOIN Table2
ON Table1.ItemType = Table2.ItemType
AND IIF(Table1.ItemSubType IS NULL, 'Empty', Table1.ItemSubType)
= IIF(Table2.ItemSubType IS NULL, 'Empty', Table2.ItemSubType)

 

[John B via AccessMonster.com]

Hi Chaim,

That worked like a charm. I wouldn't have come up with that in a million
years. Thanks a lot!

John