How to prevent #Error values derived from Nulls in an outer join

Question

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() but 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?

Answer 1

You need to apply the function (Nz) that replaces the null with another value before the week function call. Use:

week( Nz(Tab2.y, Value_for_y) , Nz(Tab2.w, Value_for_w) ) 

You probably also need tests against the special Value_for_y or Value_for_w inside week().