Trap for #NULL! Value

[Manoj Chaurasia]

Does anyone know how to trap for a #NULL! value. I used the formula if(s22 = " #NULL!" ,s1,S22& S23) and it does not work. I took the quotes out and it still did not work. Any suggestions

Posted by michael r. on May 12 2009 4:07PM { U323 , F8 , T602 , C1463 }

[Manoj Chaurasia]

I have tried just about every function to solve this same problem. ISBLANK, ISERROR, ISEMPTY, =#NULL

The only function that works for me is " ISNONTEXT" .

I use it in an IF statement

IF(ISNONTEXT(S8), PROPER(S9) & " " & PROPER(S10), S8)

If you find a better one let me konw.

Posted by Wendell D. on May 12 2009 5:53PM { U351 , C1464 }

[Brendan Peterson]

ISERROR is the best way. It will return TRUE if it is #NULL! and FALSE if its not. So it would look like

IF(ISERROR(S22), S1, S22& S23)

That will check is S22 is #NULL! and if it is, use S1. If its not, it will concatenate S22 and S23...

Posted by Brendan P. on May 12 2009 6:48PM { U40 , C1465 }

[Manoj Chaurasia]

Good one, Brendan!!! But, don't you think that there should bo something like ISNULL(SourceRef)

Posted by Vikram N. on May 18 2015 7:59PM { U5546 , C12023 }

[Manoj Chaurasia]

Hi there,

I am having the same problem as Wendell D:

I have tried to catch if a source field = " #NULL!" , and I have tried using the ISERROR formula, and it is NOT catching the #NULL! value.

I am trying to catch if a source Date field doesn't contain a value, and to use a new Date if there is no value in the field, but whatever I try (all of the above suggestions) the software does not properly parse the #NULL! value... (I have even tried the ISDATE function - this doesn't work either)

Do you know if there is a bug with SCRIBE Insight version 7.9.0.5...

Posted by James H. on Sep 21 2015 8:56AM { U5150 , C12299 }

[Manoj Chaurasia]

Can you run a test job and post the results of your ISERROR

Posted by David G. on Sep 24 2015 2:42PM { U2819 , C12313 }

[Mack Rienstra]

Important clarification, use IF(ISERROR(S##) as the first statement instead of the last.

This worked:

IF(ISERROR(S1), "Brown", IF(S1="W","White", IF(S1="G","Green","Blue")))

This did not worked:

IF(S1="W","White", IF(S1="G","Green",IF(ISERROR(S1), "Brown","Blue")))

Scribe seems to be unable to evaluate the value against alitteral value when it's looking at an error value.