Dealing with Blank Values (Nulls)
Databases have two types of fields: required and optional. Ordinarily, fields are optional, which means a sloppy person can leave a lot of blank values. These blank values are called nulls, and you need to handle them carefully.
If you want to write a filter condition that catches null values, simply type this text into the criteria box:
Is Null
This condition matches any fields that are left blank. Use this on the CustomerID field in the Orders table to find any orders that aren't linked to a customer. Or ignore unlinked records by reversing the condition, like so:
Is Not Null
Sometimes, you don't want to specifically search for (or ignore) null values. Instead, you want to swap those values with something more meaningful to the task at hand. Fortunately, there's an oddly named tool for just this task: the Nz( ) function.
The Nz( ) function takes two arguments. The first's a value (usually a query field) that may contain a null value. The second parameter's the value that you want to show in the query results if Access finds a null value. Here's an example that uses Nz( ) to convert null values in the Quantity field to 0:
Nz([Quantity], 0)
Converting to 0 is actually the standard behavior of Nz( ), so you can leave off the second parameter if that's what you want:
Nz([Quantity])
At this point, you may not be terribly impressed at the prospect of changing blank values in your datasheet into zeroes. But this function's a lifesaver if you need to create calculated fields that work with values that could be null. Consider this innocent-seeming example:
OrderItemCost: [Quantity] * [Price]
This expression runs into trouble if Quantity is null. Nulls have a strange way of spreading, somewhat like an invasive fungus. If you have a null anywhere in a calculation, the result of that calculation is automatically null. In this example, that means the OrderItemCost for that record becomes null. Even worse, if the OrderItemCost enters into another calculation or a subtotal, that too becomes null. Before you know it, your valuable query data turns into a ream of empty cells.
To correct this problem, use the Nz( ) function to clean up any potential nulls in optional fields:
OrderItemCost: Nz([Quantity]) * Nz([Price])
Finally, you can use Nz( ) to supply a different value altogether. In a text field, you may choose to enter something more descriptive. Here's an example that displays the text [Not Entered] next to any record that doesn't include name information:
Name: Nz([FirstName] & [LastName], "[Not Entered]")