Microsoft Access Programmer Fort Lauderdale, Florida

Microsoft Access Fields And Nulls

Call MS Access Solutions at (323) 285-0939 for a FREE consultation.

The material below originally appeared in Alison Balter's book Mastering Microsoft Office Access 2007 Development and is reprinted here with the author's permission. There may be references to "Figures" or "Chapters" that are not reprintable and are not used on this page.

Excluding Fields From The Output

At times, you need to include a column in your query that you don't want displayed in the query output; this is often the case with columns used solely for criteria. Figure 4.30 shows an example. If you run this query, you get the total, average, count, and maximum sales grouped by both country and order date. However, you want to group only by country and use the order date only as criteria. Therefore, you need to set the Total row of the query to Where, as shown in Figure 4.31. The column used in Where has been excluded from the query result. You can easily determine this by noting that the check box in the Show row of the Order Date column is unchecked.

Understanding 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 th 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.)

Null values can affect the results of multitable queries, queries including aggregate functions (Totals queries), and queries with calculations. Null values can also affect the result of aggregate queries. For example, if you perform a count on a field containing Null values, the Access Database Engine 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. Including a Null value in a calculation containing a numeric operator (+, -, /, *, and so on) results in a Null value. In Figure 4.32, 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.33. 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.34.

CAUTION

Nulls really cause trouble when the results of one query containing Nulls are used in another query; a snowball effect occurs. You can easily 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 zerolength strings. Be careful when doing this, though, because this function 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.

---

Author Attribution

The previous material originally appeared in Alison Balter's book Mastering Microsoft Office Access 2007 Development and is reprinted here with the author's permission.

---

Expert Microsoft Access Programmer Fort Lauderdale, FL

When you need a Microsoft Access programmer for your Fort Lauderdale, Florida, business, call MS Access Solutions at (323) 285-0939. We have over 25 years experience in Microsoft Access programmer solutions. We create Access database applications for all business sectors, including hospitals, government agencies, the U.S. military, universities, agriculture, employment services, and insurance companies. We work with the most complex Microsoft Access and SQL Server databases, as well as smaller projects, like fixing damaged Access database forms, MS Access reports, Access macros, and Visual Basic For Applications (VBA) code.

---

Get more information about our Microsoft Access programming services on the MIcrosoft Access programmer Jacksonville, Florida web page.