Binaryworld - Two handy functions for Null handling ... [ VB -> String ]

You're probably aware that most VB functions don't work well with Null values, which is an issue when you're working with database columns that can accept Nulls. For example, the following statement:

Click here to copy the following block

Dim s as String
s = rs.Fields("AnyField")

can raise error 94 "Invalid use of Null". The usual workaround is to force the conversion to string by appending an empty string, as in:

Click here to copy the following block

s = rs.Fields("AnyField") & ""

However, this solution slightly affect code readability, especially if other people in your team aren't aware of the trick. A better and more flexible solution is to build a function that can convert a Null value to any other value:

Click here to copy the following block

Function IfNull(value As Variant, Optional NullValue As Variant = "") As Variant
  If IsNull(value) Then
    IfNull = NullValue
  Else
    IfNull = value
  End If
End Function

You can use the above function with just one argument to replace the trick based on the empty string, but you can even display a more meaningful string (in reports, for example):

Click here to copy the following block

' clear the textbox if the Department is Null
txtDept = IfNull(rs.Fields("Department"))
' Display the name of the Department, or "unknown" if Null
Print "Department is " & IfNull(rs.Fields("Department"), "unknown")

Another handy function for Null handling mimicks the NullIf function in T-SQL, and is especially useful if you are importing data from a database or a text file that uses a special string - for example "Unknown" - in place of the Null constant, and you need to process these special strings as Null values:

Click here to copy the following block

Function NullIf(value As Variant, NullValue As Variant) As Variant
  If value = NullValue Then
    NullIf = Null
  Else
    NullIf = Value
  End If
End Function

For example, you can easily convert all "Unknown" string to Null as follows:

Click here to copy the following block

' read the value from a text file
Line Input#1, dept$
' store in the database, but convert
' it to Null if equal to "Unknown"
rs.Fields("Department") = NullIf(dept$, "Unknown")

Note that you can use the NullIf function with string, numeric and date values.