Alison Balteramp;#039;s Mastering Microsoft Office Access 1002003 [Electronic resources] نسخه متنی

Nulls and Query Results

Null values in your table's fields can noticeably affect query results. A Null value is different from a zero or a zero-length string, which indicates that the data doesn't exist for a particular field; a field contains a Null value when no value has yet been stored in the field. (As discussed in Chapter 2, you enter a zero-length string in a field by typing two quotation marks.)Pitfalls of Multitable Queries" section).

Null values can also affect the result of aggregate queries. For example, if you perform a count on a field containing Null values, Jet includes in the count only records having non-Null values in that field. If you want to get an accurate count, it's best to perform the count on a primary key field or some other field that can't have Null values.

Probably the most insidious problem with Nulls happens when you include them in calculations. A Null value, when included in a calculation containing a numeric operator (+, -, /, *, and so on), results in a Null value. In Figure 4.31, for example, notice that the query includes a calculation that adds the values in the Parts and Labor fields. These fields have been set to have no default value and, therefore, contain Nulls unless something has been explicitly entered into them. Running the query gives you the results shown in Figure 4.32. Notice that all the records having Nulls in either the Parts or Labor fields contain a Null in the result.

The solution to this problem is constructing an expression that converts the Null values to zero. The expression looks like this:

TotalCost: NZ([Parts])+NZ([Labor])

The NZ() function determines whether the Parts field contains a Null value. If the Parts field contains a Null value, the expression converts it to a zero and includes it in the calculation; otherwise, the expression uses the field's value in the calculation. The same expression is used to evaluate the Labor field. The result of the modified query is shown in Figure 4.33.

CAUTION

Nulls really cause trouble when the results of one query containing Nulls are used in another querya snowball effect occurs. It's easy to miss the problem and output reports with inaccurate results. Using the NZ() function eliminates this kind of problem. You can use the NZ() function to replace the Null values with zeros or zero-length strings. Be careful when doing this, though, because it might affect other parts of your query that use this value for another calculation. Also, be sure to use any function in a query on the top level of the query tree only because functions at lower levels might hinder query performance. A

query tree refers to the fact that a query can be based on other queries. Placing the criteria at the top of the query tree means that, if queries are based on other queries, the criteria should be placed in the highest-level queries.