Suppose I have two tables to outer join
Tab1: ID Nam 1 Joe 2 Moe 3 Flo Tab2: ID Val y w 1 stuff 66 33 2 duff 67 44
and one of the resulting columns is derived -- i.e., it's a function of two other columns
SELECT Tab1.*, Tab2.Val, Tab2.y, Tab2.w, week(Tab2.y,Tab2.w) AS week FROM Tab1 LEFT JOIN Tab2 ON Tab1.ID = Tab2.ID
As expected, where there is no join, nulls appear under the actual columns, but
#Error appears under the derived column:
ID Nam Val y w week 1 Joe stuff 66 33 66w33 2 Moe duff 67 44 67w44 3 Flo #Error
First, I thought I could prevent
#Error via the useful-for-handling-null-strings function
Nz(week(Tab2.y,Tab2.w)) did nothing.
Next, I thought it was something I had to handle in my
week() function -- i.e., handle cases of
IsNull(y) -- but I discovered that it doesn't even get called for the "null cases".
How to prevent
#Error values from showing?