Suppose I have two tables to outer join

ID  Nam
1   Joe
2   Moe
3   Flo

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?

  • Use week(Tab2.y, Nz(Tab2.w, ValueYouWantInstead)) You need to apply the function that replaces the null with another value before the function call. – ypercubeᵀᴹ Jun 10 '15 at 1:23
  • It worked, @ypercube! At least, i no longer get #Error. I got 0w00 which I did have to handle in my function: if y=0 ... Care to elaborate in an answer? – Martin F Jun 10 '15 at 1:33
  • What does this function (week) do? Simple concatenation? Can't you use Nz(Tab2.y, '0') & 'w' & Nz(Tab2.w, '00') AS week ? – ypercubeᵀᴹ Jun 10 '15 at 1:48
  • @ypercube - That's more or less what it does. I use it in a few places so i'd rather keep it as a function -- unless there's good reason not to do so... – Martin F Jun 10 '15 at 15:12

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().

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.