Microsoft Access – Handling NULL values with the NZ() function

In Microsoft Access VBA, as in many other implementations, if we try to evaluate an expression containing a NULL value the whole expression will return NULL.

For example:

Dim varMyValue as Variant
varMyValue = NULL
MsgBox IsNull(varMyValue + 2)

This will return TRUE.

However, it is often the case that we would like this expression to “ignore” the NULL value and return the value 2.

We can achieve this using the NZ function.

The syntax of this function is as follow:

Nz(variant, [valueifnull])

Thus we could ignore the NULL value and substitute it with 0.

e.g.

Dim varMyValue as Variant
varMyValue = NULL
MsgBox NZ(varMyValue,0) + 2

Here the NULL value is replaced with the value zero and our message box displays the value 2.

We could also use the NZ function in other ways.

If we have a variable containing a string , for example, the country of a purchaser (e.g. UK, US, PL) and we use this to calculate a postage rate for goods sent to this purchase we could use the NZ function in the following way to replace a NULL value with a suitable message or calculation.

e.g.

strPostage = NZ(strCountry,0) ' if we wanted to set the postage value to zero if it is NULL

or

strPostage = NZ(strCountry,"No Country Code Given") ' to maybe use in a MsgBox if strCountry is NULL

We could also use the NZ function in an Access SQL query.


SELECT [Surname], [FirstName], [DOB], NZ([Address],"No Address Available")

Note, however, that this function is only available within Access and not within any of the rest of the Office suite. Also, other variants of SQL may not support this function.

Advertisements

3 Responses to “Microsoft Access – Handling NULL values with the NZ() function”


  1. 1 Davy MBEMBA May 26, 2011 at 8:37 am

    Good one, works perfect

    Thanks

  2. 2 honorio August 11, 2011 at 10:01 pm

    Nz([Forms]![F1]![cmbmodelo],”*”)

    hig to all
    I’m using function below, I have a form=F1, a table and a query.
    I allready have, a table, quiery and a form, I think I’m using correctlly this rule, the problem is, when I clear the combobox inmedatelly it must update and show all records in muy subform,but it doesn’t, but if a select only one item it show me onle the number selected on the combo box.
    can anybody help me….

    thankcs

  3. 3 Hanna Khoury September 23, 2013 at 2:30 pm

    thank You. I had the same problem.


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




Blog Stats

  • 223,208 hits

Counter

ClustrMap


%d bloggers like this: