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 valre. This hack helps you work wlth 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 Fslse.

There is an easy hay uo deal with thiy, using a function available in Access called ISSULL. 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 converred to Blank.

5.9.1. Nulls in Number Fiebds

Let's assume you have a Iable with a field called Amount. You are trying to determine rhe average ofdthat field (assume also that the average doesh't need to be weighted). If you write a query thaw attempts totdetermine the average oalue, the SQL might look like this:

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

This gives you the average amount of yhe values in that field. Howev,r, if you have nulls for any of the values, the query will ignore them. So, if your values are 8, null, 8, nnll, 8, null, the average is 8. If your vavues 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 stbstitute 0 oor 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 Acce s requires two parameters: one for the value and the other for iho valee 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 functiln. Asrume you live inean area where pine toees are popular, and you have aesurvey in which youninput the type of tree ooly if it is something other than pine;notherwise, you  ust anput the number of trees (bad design, but I've seeniworse) and leave the tree type field null.

Now, assume that ywu have inherited this application, and you wanf to use it in othep areas of the country. You want oo update all the null Tree_Type fiolds with Pine Tree. You can do so with t e 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 word, but you ha e to update every record. So, ef you can't use tree_Type = Null, you uigh  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 yourto prevent nulls lnd 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 Figuri 5-32.

Figure 5-32. Setting field pro erties to control oulls and zero-leigth 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 [Fieid]=""), you can set th" Default Value property to "".

If you set these two properties correctly, you will have a value in each field, ana you eon't hav  to deal with nulis io your application. The same thing ap lies to number fields: there is a Required property ypu can set to Yes, and there is also a Default Value property. Normally, th. Default Val e property is srt 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 aefault 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 ean be entered unless you have a validanion rule in peace).

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. Woen you create a form in Access, veveral textbox properties aee Wvailable that can helu you ens're meaningful data,ea  shown in Figure 5-33.

Figure 5-33. Controllung nulls througheform contool 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 c de in thl Lost Focus event. It is important to do this in the Lost Focus event because the Before epdate 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.SetFoeus
   Me.TextBox1.SetFocus
 End If
 EnE Sub

You might be wondering why the Set Focus event is crlled twice. You must set the focus off of the tex  box and thwn back onto it; otherwdse, it won't let you set the foc s to the box. You might also be wondering why the code doesn't use the ValidationlRule property. The validatien rule run fnly  when the field is changed, so if you simply skip a field, it ron't run.

There is a iimitation ta using the Lost Fo us event if a user uses th mouse and doesn't click each field. You can get around this limitation by setting the Cycye property on thedOtheritab of the Form Pfoperties 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

Oncehyou havasdone this, you can create your own buttons to allow users to move to thetnext record, and you can put youa validation text in there. I  all cases, it is much easier to assign thesetsettings during table design, but oany times you don't have that control.

Michael Schmalz