I'm trying to tie a description to group codes to use in another table. Unfortunately the data is from a very old database and didn't have very good requirements for fields when it was initially created. So now I have a number of fields for part number group codes that were blank. I'm trying to convert these null values to say "blank". I've tried this many different ways and cannot get the nz function to modify the data in any way.
In the provided code snippet I have tried using the nz function only after select, only after from, and in both places as shown.
SELECT [Part Numbers].Part, nz([Part Numbers].Group,"Blank"), [Group Codes].Description
FROM [Part Numbers]
INNER JOIN [Group Codes] ON nz([Part Numbers].[Group],"Blank") = [Group Codes].[Group Code];
That query doesn't return records where Group field is Null. It can't even be displayed in query design with that join.
Consider RIGHT JOIN:
SELECT [Part Numbers].Part, Nz([Part Numbers].Group,"Blank") AS Expr1, [Group Codes].Description
FROM [Group Codes] RIGHT JOIN [Part Numbers] ON [Group Codes].[Group Code] = [Part Numbers].Group;