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.
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.
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.