Knowledge Base

Our Knowledge Base covers a wide variety of technical topics, from the basic to the complex.

Why doesn't the formula Nz([num1], [num2]) detect when [num1] is blank like it is supposed to? It just returns 0 instead of substituting the alternate value [num2].

This can happen because the field property for [num1] called "Treat blank as zero in calculations" is checked. This is checked by default, so you can do things like add and subtract lists of values which contain some blanks.

When this is checked, a blank field looks to formulas just like a zero, which is not the same as null. For the Nz() formula to work correctly, uncheck this box. Then blank fields will then be seen as null.

Comments

Post new comment

The content of this field is kept private and will not be shown publicly.
Type the characters you see in this picture. (verify using audio)
Type the characters you see in the picture above; if you can't read them, submit the form and a new image will be generated. Not case sensitive.

© 1997-2014 Intuit Inc. All Rights Reserved.

Online Database VeriSign Secured Web Based Software TRUSTe Certification Online Database SSAE Audit