Access Database Design and Programming, Second Edition

Miscellaneous Functions and Statements

We’ll conclude our discussion of Access VBA functions and statements by examining a hodgepodge of language constructs that perform such tasks as evaluating objects or variables, evaluating an expression, and altering program flow based on an expression’s values.

The Is Functions

VBA has several Is functions that return Boolean values indicating whether a certain condition holds. We have already discussed the IsMissing function in connection with optional arguments. Here are some additional Is functions.

The IsDate function

This function indicates whether an expression can be converted to a date. For instance, the code:

Dim x As String
x = "1/1/45"
Debug.Print IsDate(x)

will print True to the Immediate window.

The IsEmpty function

This function indicates whether a variable has been initialized. For example, the code:

Dim x As Variant
If IsEmpty(x) Then . . .

tests whether the variable x is empty.

The IsNull function

This function is used to test whether a variable or field is Null (that is, contains no data). Note that code such as:

If var = Null Then

will always return False because most expressions that involve Null automatically return Null. The proper way to determine if the variable var is Null is to write:

If IsNull(var) Then

Here is a typical scenario:

Dim rs As Recordset
Dim s As String
Set rs = CurrentDb.OpenRecordset("Names")
rs.MoveFirst
If Not IsNull(rs!LastName) Then
	s = rs!LastName
	. . .
End If

The IsNumeric function

This function indicates ...