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:

Dim s as Strings = 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:

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:

Function IfNull(value As Variant, Optional NullValue As Variant = "") As Variant    If IsNull(value) Then        IfNull = NullValue    Else        IfNull = value    End IfEnd 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):

' clear the textbox if the Department is NulltxtDept = IfNull(rs.Fields("Department"))' Display the name of the Department, or "unknown" if NullPrint "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:

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

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

' read the value from a text fileLine 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.