Null or Not Null

That is the question…

I am working on an old Visual Basic project. It is not my own, it’s just an existing project in which I have to make some changes. There’s an existing database connection and in one of “my” functions, I have run a query, and created a recordset (rs). In a loop, I am iterating through the records with a bit of code like this:

  If Not IsNull(rs!X) Then
    strX = rs!X
    strX = ""
  End If

or, if you like

  strX = IIf(IsNull(rs!X), "", rs!X)

Somehow, I get an error, “Invalid use of Null”, on the second line! So, rs!X is Null on the second line, but it wasn’t on the first line, or else the code would never have come to the second line! How is this possible?!?

I tried debugging the code, stepping through the code in the IDE. On the first line, when I watched the value of rs!X, it had some real value. Then, either the program would continue on the second line, but result in the above error, or the code would jump to the fourth line, as if the value had been Null!

I suppose there’s something wrong with either the recordset or the database connection, but I cannot yet figure it out. The only way I have got it to work, so far, is with an intermediate Variant-type variable, because a Variant can be assigned a Null value:

  varX = rs!X
  strX = IIf(IsNull(varX), "", varX)


Saturday, January 19th, 2008 Computing

No comments yet.

Leave a comment