CODEWITHSUNDEEP
vbams-access
Jaehaerys68
Jaehaerys68

Reputation: 51

VBA Access - FindFirst Null Value

I'm trying to findfirst null value on the following code. I could just use where in the select but it will remove the non null values. I want to keep the whole recordset so I can report back to the user which row is null using rs.AbsolutePosition. I've basically set it up to compare the users entered information in the temp table against another table. If the matching string is in there it appears, otherwise should be null on the recordset.

strSQL = "SELECT Tbl_Temp.ID, Tbl_List.String FROM Tbl_Temp LEFT JOIN Tbl_List ON Tbl_Temp.string = Tbl_List.string"
Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
strIsN = "IsNull(string)"

rs.FindFirst strIsN
    If rs.NoMatch Then
        MsgBox "no null values"
    Do While Not rs.NoMatch
        MsgBox "Found null" & rs.AbsolutePosition
        rs.FindNext strIsN
    Loop
    rs.FindNext strIsN
End If

It basically does nothing right now. Doesn't even trigger an error or no match. What am I doing wrong?

Upvotes: 1

Views: 1974

Answers (1)

Andre
Andre

Reputation: 27634

Your code indentation is off and fooled both you and me.

This is what you want - you were missing the Else.

RS.FindFirst strIsN
If RS.NoMatch Then
    MsgBox "no null values"
Else
    Do While Not RS.NoMatch
        Debug.Print "Found null " & RS.AbsolutePosition
        RS.FindNext strIsN
    Loop
End If

Please read How to debug VBA code, stepping through your code would have found the issue instantly.

Both "IsNull(string)" and "[String] Is Null" will work, but "[String] Is Null" will probably perform a little better.

Upvotes: 1

Related Questions