Hack 47. Don't Let Nulls Ruin Data Summaries

When nulls are mixed in with valid data, incorrect results can occur. Here are some guidelines to tame the beast.

When you are dealing with values in Access, you might be tempted to think that a blank field is simply a blank field. However, there is a difference between a blank field that is filled in with an empty string and a blank field that is null. For example, when you are looking at number fields, there is a difference between a field with a 0 value and a field with a null value. This hack helps you work with these nonvalue values.

The first frustrating thing about nulls is that if you write a line such as this, every line will show up as Not Blank, even if you have null values:

	IIF([Amount]=Null,"Blank","Not Blank")

This occurs because in a Boolean expression, any item compared to Null returns False.

There is an easy way to deal with this, using a function available in Access called ISNULL. This function returns a Boolean and allows you to perform your test. Here is how to rewrite the previous example:

	IIF(ISNULL([Amount],"Blank","Not Blank")

That clinches it. Now, any encountered null is converted to Blank.

5.9.1. Nulls in Number Fields

Let's assume you have a table with a field called Amount. You are trying to determine the average of that field (assume also that the average doesn't need to be weighted). If you write a query that attempts to determine the average value, the SQL might look like this:

	SELECT Avg(tbl_Amount.Amount) AS AvgOfAmount
	FROM tbl_Amount;

This gives you the average amount of the values in that field. However, if you have nulls for any of the values, the query will ignore them. So, if your values are 8, null, 8, null, 8, null, the average is 8. If your values are 8, 0, 8, 0, 8, 0, the average is 4. Depending on the purpose of the query, you might want to see 4 instead of 8.

If you want to substitute 0 for null, you can try to do it with the ISNULL function by writing a line such as this:

	IIF(ISNULL([Amount]),0,[Amount])

There is a much easier way, though. The NZ function available in Access requires two parameters: one for the value and the other for the value if it is null. You can use this for both number and string functions. Here is what the SQL of the query looks like using the NZ function:

	SELECT Avg(NZ([Amount],0)) AS AverageofAmount
	FROM tbl_Amount;

As you can see, this is more compact than writing out IIF statements to perform the same function.

Next, let's look at an example of a string function. Assume you live in an area where pine trees are popular, and you have a survey in which you input the type of tree only if it is something other than pine; otherwise, you just input the number of trees (bad design, but I've seen worse) and leave the tree type field null.

Now, assume that you have inherited this application, and you want to use it in other areas of the country. You want to update all the null Tree_Type fields with Pine Tree. You can do so with the NZ function. Here is what the SQL for this query looks like:

	UPDATE tbl_TreeTypes SET tbl_TreeTypes.Tree_Type =
	nz([Tree_Type],"Pine Tree");

This will work, but you have to update every record. So, if you can't use tree_Type = Null, you might ask if you can use null for criteria in a query. You can, using one of two methods. The easiest way is to use IS NULL for the criteria. The previous query looks like this using IS NULL:

	UPDATE tbl_TreeTypes SET tbl_TreeTypes.Tree_Type = "Pine Tree"
	WHERE (((tbl_TreeTypes.Tree_Type) Is Null));.

5.9.2. Preventing Nulls

It might be necessary for you to prevent nulls and zero-length strings in your database in the first place. A good example for this might be a name field or a ZIP code field. You can do this through either your table design or your data entry forms.

5.9.2.1 Table design to prevent nulls and zero-length strings.

When you design your table, you can set several properties to help you handle blank fields, as shown in Figure 5-32.

Figure 5-32. Setting field properties to control nulls and zero-length strings

The first is a property called Required. If you enter Yes for the Required property, you are telling Access a value must be entered in this field for it to be saved. This won't prevent someone filling it with a zero-length string. Setting the Allow Zero Length property to No forces an entry other than a zero-length string. If you say Yes, and you just want to eliminate nulls (test for blank by writing [Field]=""), you can set the Default Value property to "".

If you set these two properties correctly, you will have a value in each field, and you won't have to deal with nulls in your application. The same thing applies to number fields: there is a Required property you can set to Yes, and there is also a Default Value property. Normally, the Default Value property is set to 0 in a number field. However, if you want to ensure that users enter a value in this field and don't simply skip over it, you can remove the 0 in the default value field and set the Required property to Yes. This ensures that the record isn't saved until the user puts a value in the field (0 can be entered unless you have a validation rule in place).

5.9.2.2 Form design to prevent nulls and zero-length strings.

If you don't have control over the table design, but you want to ensure the data entered is accurate, you can do so through Access forms. When you create a form in Access, several textbox properties are available that can help you ensure meaningful data, as shown in Figure 5-33.

Figure 5-33. Controlling nulls through form control properties

You can set the Default Value property to allow a zero-length string if all you want is avoid a null.

You can also write code in the Lost Focus event. It is important to do this in the Lost Focus event because the Before Update event won't fire if the field is just tabbed through, and the After Update event fires after the field has actually been changed. Here is what that code might look like for a text box called TextBox1:

	Private Sub TextBox1_LostFocus( )
	If IsNull(Me.TextBox1.Value) Then
      MsgBox "You must enter a value in this field", vbOKOnly, "Important"
	  Me.TextBox2.SetFocus
	  Me.TextBox1.SetFocus
	End If
	End Sub

You might be wondering why the Set Focus event is called twice. You must set the focus off of the text box and then back onto it; otherwise, it won't let you set the focus to the box. You might also be wondering why the code doesn't use the Validation Rule property. The validation rule run onlys when the field is changed, so if you simply skip a field, it won't run.

There is a limitation to using the Lost Focus event if a user uses th mouse and doesn't click each field. You can get around this limitation by setting the Cycle property on the Other tab of the Form Properties dialog box to Current Record (as shown in Figure 5-34) and then setting the Navigation Buttons property to No on the Format tab of the same dialog box (as shown in Figure 5-35).

Figure 5-34. Setting the Cycle property to Current Record

Figure 5-35. Setting the Navigation Buttons property to No

Once you have done this, you can create your own buttons to allow users to move to the next record, and you can put your validation text in there. In all cases, it is much easier to assign these settings during table design, but many times you don't have that control.

Michael Schmalz