When a field is empty, it has a NULL value - a nothing value. This is very important to understand when working with SQL, because any calculation or concatenation that contains a NULL value, will inevitably return a NULL answer - no matter which data type you are working with. Following are some functions and examples that can be used to avoid NULL values.
Functions that can be used to convert NULL values to usable values
- iif() - IIF takes three arguments. If the first evaluates to true, the second argument is returned; otherwise the third is returned. eg.
- coalesce() - The COALESCE function takes two or more arguments and returns the value of the first non-NULL argument. If all the arguments evaluate to NULL, the result is NULL.
Numerical data types
When working with numerical types, you would mostly convert NULL values to 0. You need to be mindful that dividing by 0 will result in an error. Examples follow:
iif(FIELDNAME is null, 0, FIELDNAME)
Text / string data types
When working with text strings, you would mostly convert NULL values to ''. In a concatenation, however, you could add or remove complete strings based on whether or not the field has a NULL value. Examples follow:
coalesce('Printed in '||COLOURS||' colours', 'Not Printed')
iif(COLOURS is null, 'Not Printed', 'Printed in '||COLOURS||' colours')