NULL problem

Im using SQL server at the backend, and in one of the tables I hv a smalldatetime datatype field- TrainingStartDate.

Many records in the db have this field set to Null

Now, to avoid other problems If I want to check it for NULL values ,I'm unable to do so. Iv tried the following but all conditions return FALSE.

if rs("TrainingStartDate") = Null

if rs("TrainingStartDate") = "Null"

if rs("TrainingStartDate") = vbNull

Why bother trapping the null fields. If you are going to assign them to a vb string, then just append vbnullstring, and that'll keep vb happy:

sString = rs(0) & vbNullString

Alternatively, you could use the ISNULL in your SQL

Access has a useful function for dealing with nulls seeing as they do crash most things its an annoying setting but hey ho.

The function in Access is NZ.
Now below is my VB equivalent.

public function Nnz(byval vara as variant, byval varb as variant) as variant
nnz=iif(isnull(vara),varb,vara)
end function


You pass in the variable/recordset field to be checked and a default you want to return if it is null, it decides and returns it.

Any use?

Vince

A standard way to avoid problems with database NULL values is to use the IsNull() function in VB. You pass the value of a field to this function and if the value is NULL, the function returns true.

VB Code:

Set rs = db.OpenRecordSet("SELECT * FROM Cust_Master")
With rs
   Do While Not .EOF
      If IsNull(.Fields("Cust_Name")) Then
         MsgBox "This customer has no name."
      Else
         MsgBox .Fields("Cust_Name")
      End If
      .MoveNext
   Loop
   .Close
End With
Set rs = Nothing

Then there is another work-around, which works towards saving some code, and is a handy tool in some cases. That is to append an empty string, either the constant vbNullString or simply "" to the field's value before using it. For e.g. the above code could be re-written as:

VB Code:

Set rs = db.OpenRecordSet("SELECT * FROM Cust_Master")
With rs
   Do While Not .EOF
      If .Fields("Cust_Name") & "" = "" Then
         MsgBox "This customer has no name."
      Else
         MsgBox .Fields("Cust_Name")
      End If
      .MoveNext
   Loop
   .Close
End With
Set rs = Nothing

Note that in the above example if you did not append "" to the field value before comparing it against "", and if the field had a NULL value, it would generate an error.

Now comes a tricky part. Let's assume you need to display the names only if they start from A. If you write the following code based on the IsNull() function:

VB Code:

Set rs = db.OpenRecordSet("SELECT * FROM Cust_Master")
With rs
   Do While Not .EOF
      If Not IsNull(.Fields("Cust_Name")) And Left(.Fields("Cust_Name"), 1) = "A" Then
         MsgBox .Fields("Cust_Name")
      End If
      .MoveNext
   Loop
   .Close
End With
Set rs = Nothing

In the above code you seem to have done everything right. The If condition will evaluate true only if the field is not NULL and starts with "A". But here is the hitch. The AND operator works in such a way that it will evaluate both the expressions before returning True or False. So it will evaluate both IsNull and the Left() functions. If the field has a NULL value, the IsNull will return False, but AND will still try to evaluate the Left() function and that will return an error because the field is NULL. To avoid such problems, it's better to append the empty string to the field value:

VB Code:

Set rs = db.OpenRecordSet("SELECT * FROM Cust_Master")
With rs
   Do While Not .EOF
      If Left(.Fields("Cust_Name") & "", 1) = "A" Then
         MsgBox .Fields("Cust_Name")
      End If
      .MoveNext
   Loop
   .Close
End With
Set rs = Nothing

Not only shorter code, but no pitfalls, too!

.