How can you check for null in a VBA DAO record set?

Question

I have an optional field in a database that I'm pulling out using a DAO Record Set. I need to check whether or not the field is set before I concatenate it with other fields. So far I have the following code snippet which I've tried with both Is and = (that's the obviously wrong syntax [[Is | =]]) to no avail. It appears that if I use = it will not correctly compare with Null and if I use Is then it complains that it's not comparing with an Object.

While Not rs.EOF
    If rs.Fields("MiddleInitial") [[Is | =]] Null Then thisMiddleInitial = "" Else thisMiddleInitial = rs.Fields("MiddleInitial")
    If prettyName(myLastName, myFirstName, myMiddleInitial) = prettyName(rs.Fields("LastName"), rs.Fields("FirstName"), thisMiddleInitial) Then
        MsgBox "Yay!"
    End If
    rs.MoveNext
Wend

If there's a simpler way to do this I'm totally open to it. prettyName takes 3 Strings as parameters and initially I was just trying to pass rs.Fields("MiddleName") directly but it threw up at a Null value. I'd prefer to do something more direct like that but this is the best I could come up with.

Answer 1

How about:

IsNull(rs.Fields("MiddleInitial").Value)

You could also have a look at this article which has some explanation about Null values in Access VBA apps and how to handle them.

Answer 2

For the example you show, Nz would work:

    thisMiddleInitial = Nz(rs!MiddleInitial,"")

Or simply concatenating the string with an empty string:

    thisMiddleInitial = rs!MiddleInitial & ""

Answer 3

Your question has been answered by Remou, seems to me, but it occurs to me that you may just be trying to get proper concatenation of the name fields. In that case, you could use Mid() and Null propagation in VBA to get the result.

I don't use separate middle initial fields, so my usual name concatenation formula is:

Mid(("12" + LastName) & (", " + FirstName), 3)

The "12" string at the beginning is going to be tossed away if LastName is Not Null and ignored if it is null, because the + concatenation operator propagates Nulls.

To extend this to include middle intials would look like this:

Mid(("12" + LastName) & (", " + FirstName) & (" " + MiddleInitial), 3)

Assuming your UDF is not doing some kind of complicated cleanup of nicknames/abbreviations/etc., this could replace it entirely, seems to me.