Login | Register   
RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX

Tip of the Day
Language: VB4,VB5,VB6
Expertise: beginner
Nov 6, 1999

Two handy functions for Null handling

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

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
        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):

' 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:

Function NullIf(value As Variant, NullValue As Variant) As Variant
    If value = NullValue Then
        NullIf = Null
        NullIf = Value
    End If
End Function
For example, you can easily convert all "Unknown" string to Null as follows:

' 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.
Francesco Balena
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date