Using access vba to check if table column has null values

Question

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

why not SQLEID = "SELECT sum(1) FROM [table_1] WHERE IsNull([column1])" and check what it returns? Or even shorter: If DCount(1, "table_1", "IsNull([column1])")>0 Then. Also check spelling: SQLEID = "SELECT..." but cdb.OpenRecordset(sqlid ..) and add Option Explicit — Dmitry Pavliv
– Dmitry Pavliv, Commented May 7, 2014 at 16:10
The query ** "SELECT sum(1) FROM [table_1] WHERE IsNull([column1])"** you specified is returns 68. Says item not found in the collection — sam
– sam, Commented May 7, 2014 at 16:20
if it returns 68, that means that you have 68 records with null values. What is the problem now? — Dmitry Pavliv
– Dmitry Pavliv, Commented May 7, 2014 at 16:25

Jimmy Smith · Accepted Answer · 2014-05-07 16:26:31Z

1

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

answered May 7, 2014 at 16:26

Jimmy Smith

2,4511 gold badge16 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using access vba to check if table column has null values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related