When it is desirable to return a zero (or another value) rather than an empty field, Access (Visual Basic) has a function Nz():
The Nz function has the following arguments.
- 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.
This example demonstrates how you can simplify an IIF function
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:
Download a sample from:
See all Topics