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.
0 Responses to “Microsoft Access – Handling NULL values with the NZ() function”