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

Working with Empty and Null

Empty and Null are values that can exist only for Variant variables. They're different from one another and different from zero or a zero-length string. At times, you need to know whether the value stored in a variable is zero, a zero-length string, Empty, or Null. You can make this differentiation only with Variant variables.

Working with Empty

Variant variables are initialized to the value of Empty. Often, you need to know whether a value has been stored in a Variant variable. If a Variant has never been assigned a value, its value is Empty. As mentioned, the Empty value is not the same as zero, Null, or a zero-length string.

It's important to be able to test for Empty in a runtime environment. This can be done by using the IsEmpty function, which determines whether a variable has the Empty value. The following example tests a String variable for the Empty value:

Sub StringVar()
Dim strName As String
Debug.Print IsEmpty(strName) 'Prints False
Debug.Print strName = " 'Prints True
End Sub

The Debug.Print statement prints False. This variable is equal to a zero-length string because the variable is initialized as a String variable. All String variables are initialized to a zero-length string. The next example tests a Variant variable to see whether it has the Empty value:

Sub EmptyVar()
Dim vntName As Variant
Debug.Print IsEmpty(vntName) 'Prints True
vntName = "
Debug.Print IsEmpty(vntName) 'Prints False
vntName = Empty
Debug.Print IsEmpty(vntName) 'Prints True
End Sub

A Variant variable loses its Empty value when any value has been stored in it, including zero, Null, or a zero-length string. It can become Empty again only by storing the keyword Empty in the variable.

Working with Null

Null is a special value that indicates unknown or missing data. Null is not the same as Empty, nor is one Null value equal to another one. Variant variables can contain the special value called Null.

Often, you need to know whether specific fields or controls have never been initialized. Uninitialized fields and controls have a default value of Null. By testing for Null, you can make sure fields and controls contain values.

If you want to make sure that all fields and controls in your application have data, you need to test for Nulls. This can be done by using the IsNull function:

Sub NullVar()
Dim vntName As Variant
Debug.Print IsEmpty(vntName) 'Prints True
Debug.Print IsNull(vntName) 'Prints False
vntName = Null
Debug.Print IsNull(vntName) 'Prints True
End Sub

Notice that vntName is equal to Null only after you explicitly store the value of Null in it. It's important to know not only how variables and Null values interact, but also how to test for Null within a field in your database. A field contains a Null if data hasn't yet been entered in the field, and the field has no default value. In queries, you can test for the criteria "Is Null" to find all the records in which a particular field contains a Null value. When dealing with recordsets (covered in Chapter 14, "What Are ActiveX Data Objects and Data Access Objects, and Why Are They Important?"), you can also use the IsNull function to test for a Null value in a field. Here's an example:

Sub LoopProjects()
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
'Open a recordset based on the Projects table
rst.Open "tblProjects", CurrentProject.Connection
'Loop through all of the records in the recordset
Do Until rst.EOF
'Print the ProjectID and the ProjectName
Debug.Print rst!ProjectID, rst!ProjectName
'If the ProjectBeginDate field is null,
'display a message to the user
If IsNull(rst!ProjectBeginDate) Then
Debug.Print "Project Begin Date Contains No Value!!"
End If
'Move to the next row in the recordset
rst.MoveNext
Loop
End Sub

Alternatively, you could use the more compact Nz function to detect Nulls and print a special message:

Sub LoopProjects2()
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
'Open a recordset based on the Projects table
rst.Open "tblProjects", CurrentProject.Connection
'Loop through all of the rows in the recordset
Do Until rst.EOF
'Print the ProjectID and the ProjectName
Debug.Print rst!ProjectID, rst!ProjectName
'Print the ProjectBeginDate, or a message if
'the ProjectBeginDate is null
Debug.Print Nz(rst!ProjectBeginDate, _
"Project Begin Date Contains No Value!!")
rst.MoveNext
Loop
End Sub

Chapter 14 covers all the concepts of recordset handling. For now, you need to understand only that this code loops through each record in tblProjects. It uses the IsNull function to evaluate whether the ProjectBeginDate field contains a Null value. If the field does contain a Null, the code prints a warning message to the Immediate window. Here is another example:

Private Sub Form_Current()
Dim ctl as Control
'Loop through each control in the form's
'Controls collection
For Each ctl In Controls
'If the control is a TextBox
If TypeOf ctl Is TextBox Then
'If the value in the control is null,
'change the BackColor property to cyan
If IsNull(ctl.Value) Then
ctl.BackColor = vbCyan
'If the value in the control is not null
'change the BackColor property to white
Else
ctl.BackColor = vbWhite
End If
End If
Next ctl
End Sub

The code in this example (found in the frmProjects form in CHAP12EX.MDB) loops through every control on the current form. If the control is a text box, the routine checks to see whether the value in the text box is Null. If it is, the BackColor property of the text box is set to Cyan; otherwise, it's set to White. If the control is not a text box, the code ignores it and moves to the next control.

You should know about some idiosyncrasies of Null:

• Expressions involving Null always result in Null. (See the next example.)

• A function that's passed a Null usually returns a Null.

• Null values propagate through built-in functions that return variants.

The following example shows how Null values are propagated:

Sub PropNulls()
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
'Open a recordset based on the Projects table
rst.Open "tblProjects", CurrentProject.Connection
'Loop through the recordset
Do Until rst.EOF
'Print the ProjectID and the value of the
'ProjectBeginDate plus one
Debug.Print rst!ProjectID, rst!ProjectBeginDate + 1
'Move to the next row
rst.MoveNext
Loop
End Sub

Figure 12.3 illustrates the effects of running this routine on a table in which the first and third records contain Null values. Notice that the result of the calculation is Null for those records because the Null propagated within those records.

Notice the difference from the previous example if the value in the field is Empty:

Sub EmptyVersusNull()
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
'Open a recordset based on the Projects table
rst.Open "tblProjects", CurrentProject.Connection
'Loop through the recordset
Do Until rst.EOF
'Print the ProjectID and the PurchaseOrderNumber
'combined with the word "Hello"
Debug.Print rst!ProjectID, rst!PurchaseOrderNumber + "Hello"
'Move to the next row
rst.MoveNext
Loop
End Sub

In this example, the tblProjects table has four records. The PurchaseOrderNumber for the first record contains a Null; for the third record, it contains an Empty. Notice the different effects of the two values, as shown in Figure 12.4.

Looking at Figure 12.4, you can see that Null printed for the first record, and Hello printed for the third record.

The EmptyVersusNull routine uses a numeric operator (+). As discussed, the effect of Null used in a calculation is a resulting Null. In text strings, you can use an ampersand (&) instead of a plus (+) to eliminate this problem. Figure 12.5 illustrates the same code with an ampersand to concatenate rather than add. You can see that no Null values result from the concatenation.

It's very common to create a generic routine that receives any value, tests to see whether it's Null, and returns a non-Null value. An example is the CvNulls function:

Function CvNulls(vntVar1 As Variant, vntVar2 As Variant) _
As Variant
'If first variable is null, return the second variable
'otherwise, return the first variable
CvNulls = IIf(IsNull(vntVar1), vntVar2, vntVar1)
End Function

You would call this routine as follows:

Sub TestForNull(vntSalary As Variant, vntCommission As Variant)
'Add the result of calling the CVNulls function,
'passing the salary and zero to the
'result of calling the CVNulls function
'passing the commission and zero
curTotal = CvNulls(vntSalary, 0) + CvNulls(vntCommission, 0)
'Display the total of salary plus commission
MsgBox curTotal
End Sub

The TestForNull routine receives two parameters: salary and commission. It adds the two values to determine the total of salaries plus commissions. Ordinarily, if the value of either parameter is Null, the expression results in Null. The code eliminates the problem with the CvNulls function, which also receives two parameters. The first parameter is the variable being tested for Null; the second is the value you want the function to return if the first parameter is determined to be Null. The routine combines the Immediate If (IIf) function and the IsNull function to evaluate the first parameter and return the appropriate value.

NOTE

If you simply want to substitute a value for a Null, the built-in function NZ can be used instead of a user-defined function. The user-defined function offers more functionality, when necessary.