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.

Guay

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.

Revocation

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 & ""

Jennette

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.

Helvetii

Clever trick, but relying on this rather obscure difference between the + and & operators is risky, imo. If someone goes back later and tries to clean up the code, they may decide to replace all +'s with &'s, or vice versa, for the sake of consistency, without realizing that it changes the behavior. – Mccutchen

I don't really worry too much about the possibility of someone who doesn't understand the basics of Jet SQL's concatenation operators messing things up. They could just as easily mess up a UDF created for the same purpose simply because they don't understand how it works. – Helvetii

Agreed, knowing how operators work is fairly fundamental stuff. – Harney

Answer 4

If rst.Fields("MiddleInitial").Value = "Null" Then

This works for me. I use MS SQL Database.

Blacking

Answer 5

I think the NoMatch option might work in this situation:

If rs.NoMatch = True Then

Blue

Answer 6

I prefer using the below to account for both Null and Empty string values. It's a good check to use you have forms collecting values from users.

If Trim(rs.Fields("MiddleInitial") & "") = "" then

Educatory