CODEWITHSUNDEEP
ms-accessvbams-access-2010
sam
sam

Reputation: 945

Using access vba to check if table column has null values

I'm trying to find if the specified column has null values or no data.if null values are present in the column then gives the message box to user saying column contain nulls. My vba 

Dim sqlid As String
Dim rst As Recordset
Dim cdb As Database

Set cdb = CurrentDb
SQLEID = "SELECT * " & _
   "FROM table_1 ;"
'
  Set rst = cdb.OpenRecordset(sqlid , dbOpenSnapshot)
   Do While Not rst.EOF
   If IsNull(rst.Fields("column1").Value) Then
   MsgBox "Has nulls"
   End If

   Loop   

    rst.Close
    Set rst = Nothing
    Set cdb = Nothing

But when i'm running this my access goes not responding. How do i check if the column has any nulls using vba

Upvotes: 1

Views: 10219

Answers (1)

Jimmy Smith
Jimmy Smith

Reputation: 2451

It hangs as your recordset is not being incremented, you need a MoveNext

Do While Not rst.EOF
   If IsNull(rst.Fields("column1").Value) Then
   MsgBox "Has nulls"
   End If
   rst.MoveNext
Loop

Simoco had a better suggestion for accomplishing this,

If DCount(1, "table_1", "IsNull([column1])")>0 Then
MsgBox "Has nulls"
End If

Upvotes: 1

Related Questions