Make Null Zero

It's nothing

When it is desirable to return a zero (or another value) rather than an empty field, Access (Visual Basic) has a function Nz():

Nz(variant, [valueifnull])

The Nz function has the following arguments.

variant

A variable of data type Variant. Optional (unless used in a query). A Variant that supplies a value to be returned if the variant argument is Null. This argument enables you to return a value other than zero or a zero-length string.

valueifnull

This example demonstrates how you can simplify an IIF function

Instead of:

varTemp = IIf(IsNull(varFreight), 0, varFreight)
varResult = IIf(varTemp > 50, "High", "Low")

You could use:

varResult = IIf(Nz(varFreight) > 50, "High", "Low")

Helen Feddema offers a suggestion about forcing a zero when Nz() doesn't work

When you want to display zeroes in text boxes (or datasheet columns) when there is no value in a field, the standard method is to surround the value with the Nz() function, to convert a Null value to a zero. However, this doesn't always work, especially in Access 2003, which is much more data type-sensitive than previous versions. In these cases, you can force a zero to appear instead of a blank by using two functions: first Nz() and then the appropriate numeric data type conversion function, such as CLng or CDbl. Here is a sample expression that will yield a zero when appropriate:

NoAvailable: CLng(Nz([QuantityAvailable]))

Download a sample from:
ACCESS Watch



See all Topics