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

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.

For the example you show, Nz would work:

    thisMiddleInitial = Nz(rs!MiddleInitial,"")

Or simply concatenating the string with an empty string:

    thisMiddleInitial = rs!MiddleInitial & ""

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.