Discussions

Expand all | Collapse all

Problems with IsNull

  • 1.  Problems with IsNull

    Posted 02-05-2019 15:17
    I am just curious if anyone else has had probelms with IsNull (or not IsNull) delivering inconsistent results? I have the below formula that works on (possibly null) summary fields.

    If( not IsNull([Qty Drop Shipped]) , [Qty Drop Shipped], Nz([Planned Drop Qty]) )

    Problem is that this formula doesn't calculate properly with (not) IsNull function. It only shows 0 when it should show 1 because Planned Drop Qty =1 and even displays that way on the record. I was playing around and changed it to the below and got the desired result. 

    If( [Qty Drop Shipped] <> null , [Qty Drop Shipped], Nz([Planned Drop Qty]) )

    Are there other limitations to IsNull other than the text and boolean limitation stated in the documentation? Any clue why this would be an issue? I use IsNull in many other places and sometimes I have issues like these and sometimes I don't. 

    Any help is appreciated. Thanks


  • 2.  RE: Problems with IsNull

    Posted 02-05-2019 17:14
    On the field property for the Summary field there is a checkbox for whether to treat blank as zero. I have never had incorrect results however I surely have the perception that about half my summary fields to get created with that check box checked and the other half get created with the check box unchecked and I have never really figured out the pattern to that.