What is Function NZ()?

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…

Scenario

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:

  1. 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.

  2. After using NZ() function: In order to make the tool ignore the NULL value, use the NZ() function as follows:

                               ShippingCost=[Shipping Fee]+NZ([Taxes])

    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.

  3. More on NZ() Function: In order to see how NZ() function works on different values, you’ll see that:

    1. 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).
    2. If the variant contains values other than NULL value, then it would simply return the same value that it is holding.

  4. 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.  

Conclusion

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.

About these ads

One response to “What is Function NZ()?

  1. Clear and concise. I have learned stuff I had not know previously. Thanks

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s