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

ms-access null vba dao
Tim Visher picture Tim Visher · Mar 19, 2009 · Viewed 59.3k times · Source

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

Hamish Smith picture Hamish Smith · Mar 19, 2009

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.

Related questions

How to I say Is Not Null in VBA

Hi I have the following expression. I'm trying to say "if the second field Is Not Null". Can you help. Thanks =Iif((Fields!approved.Value = "N" & Fields!W_O_Count.Value IsNotNull), "Red", "Transparent")

Read More
where to place CASE WHEN column IS NULL in this query

I'm having some trouble translating an MS Access query to SQL: SELECT id, col1, col2, col3 FROM table1 LEFT OUTER JOIN table2 ON table1.id = table2.id LEFT OUTER JOIN table3 ON table1.id = table3.id so far so good, …

Read More
How to do INSERT into a table records extracted from another table

I'm trying to write a query that extracts and transforms data from a table and then insert those data into another table. Yes, this is a data warehousing query and I'm doing it in MS Access. So basically I want …

Read More