In the previous post, you have seen that if one of the value is NULL, then the output of the + operator is also NULL.
Lets consider the following example:
O = A + B
If B=NULL, then the expression “O = A + Null” will be equal to Null. But in real life, when you are adding two values and if you encounter NULL, then you want MS Access to ignore NULL value and perform the addition operation as normal i.e. you want the final output to be displayed as :
O = A + 0 i.e. O = A
Let us see how this function can be used to achieve the desired result…
Consider the table “Orders” using which you want to find the total shipping cost by adding the columns [Shipping Fee] and [Taxes] :
Shipping Cost = [Shipping Fee]+[Taxes]
As discussed in the previous post, if the value of Taxes is NULL in one of the record, then you’ll get NULL as an output. However, you want MS Access to ignore the NULL value and just return the [Shipping fee] value for that record.
So, you’ll create the query using the query wizard as shown in the screenshot below:
On running the query, you’ll get the following output:
Now, let us understand what the problem is and how we can avoid/solve this problem:
Before using NZ() function: In the screenshot above, you’ll find that if you use only the + operator, then in the highlighted row, even if the value in the column [Shipping fee] is $50.00, you’ll get “NULL Value” as an output because value in column [Taxes] is NULL.
But in reality, you would want the MS Access tool to ignore the NULL value and simply return $50.00 as the final output.
After using NZ() function: In order to make the tool ignore the NULL value, use the NZ() function as follows:
What happens here is that NZ() function returns the Zero Length String i.e. ZLS, if the value of the variant is a NULL value. So, on using this function, the output of the highlighted row will be $50.00 and not NULL.
More on NZ() Function: In order to see how NZ() function works on different values, you’ll see that:
If the variant is NULL value, then as seen from the screenshot above, you’ll see a blank which is actually a Zero Length String (ZLS).
If the variant contains values other than NULL value, then it would simply return the same value that it is holding.
Making NZ() display Customised message: Further, in some cases you might want the NZ() function to specify a customised value or message if it encounters a NULL value. This can be achieved by constructing the NZ() function as follows:
NZ([Taxes], “Missing Value”)
As seen above, the NZ() function will return the text “Missing value” whenever it encounters the NULL value.
In this post, you have learnt how to use the NZ() function to manage NULL values while performing data operations.
If you have happened to use this function, please share with everyone when and how you have used it.