Testing if fields are null ms access

Question

Im trying to create a SQL statement in MS Access that will look for blank fields in a table. Say, for example, a patients last name is missing - this query should pick this up.

How would I go about doing this? I'm able to determine how to do it for the first record but for an entire table is proving difficult

Dim Rst As recordSet
Dim f As Field

'Current Record set
Set Rst = CurrentDb.OpenRecordset("tblWebMeetingData")

'Holds current fields data
Dim fieldData

'With Rst
    'Do Until Rst.EOF
       For Each f In Rst.Fields
            If IsNull(f.Value) Then
                MsgBox ("Field Name: " & f.Name)
            End If
       Next
    'Loop
'End With

Rst.Close

user2088176 · Accepted Answer · 2013-03-07 22:11:57Z

2

More elegantly:

Dim i As String
i = "tblWebMeetingData"
Dim j As Integer
Dim rst As Recordset

' For each Field in the table
For j = 0 To CurrentDb.TableDefs(i).Fields.Count - 1
    ' Return the number of lines that are null
    Set rst = CurrentDb.OpenRecordset("SELECT count(*) FROM " & CurrentDb.TableDefs(i).Name & " WHERE " & CurrentDb.TableDefs(i).Fields(j).Name & " IS NULL")
    rst.MoveFirst
    ' Check if it's more than one
    If rst.Fields(0).Value > 0 Then
        MsgBox CurrentDb.TableDefs(i).Fields(j).Name
    End If
Next

edited Mar 7, 2013 at 22:11

answered Mar 7, 2013 at 22:06

user2088176

2461 silver badge11 bronze badges

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

4 Comments

Katana24 Over a year ago

this worked a treat but I had to add the [] because some of the field names have spaces - kudos!

Katana24 Over a year ago

kind of a follow up but how would i go about finding the exact row this occurred in?

user2088176 Over a year ago

Instead of count(*), you can use the key. It would return the key for every field that is null. Then, you would have to add a Do until rst.EOF to loop through all the results.

user2088176 Over a year ago

Also, if you would like to loop through all tables of the database, simplify replace Dim i As String for Dim i As Integer and add a for loop, from 0 to CurrentDb.TableDefs.Count - 1 while checking that it is not a system table (easily verified by If Left(CurrentDb.TableDefs(i).Name, 4) <> "MSys" Then).

HansUp · Accepted Answer · 2013-03-07 22:17:13Z

2

Looks to me like your code would do what you want if you use MoveNext before Loop. I make a few other minor changes in this version.

Dim Rst As DAO.recordSet
Dim f As DAO.Field
Dim db As DAO.Database

'Current Record set
Set db = CurrentDb
Set Rst = db.OpenRecordset("tblWebMeetingData", dbOpenTable, dbOpenSnapshot)

With Rst
    Do While Not .EOF
       For Each f In .Fields
            If IsNull(f.Value) Then
                MsgBox "Field Name: " & f.Name
            End If
       Next
       .MoveNext
    Loop
End With

Rst.Close

However, in the question you said you want to "create a SQL statement in MS Access that will look for blank fields in a table". But, instead of a query you showed us VBA code which inspects a DAO.Recordset. I'm not sure what you really want.

edited Mar 7, 2013 at 22:17

answered Mar 7, 2013 at 22:09

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

1 Comment

Katana24 Over a year ago

oh sorry bad wording - i meant SQL code in regards to a query

Mike · Accepted Answer · 2013-03-08 14:38:12Z

1

Why not just:

Public Sub CheckNull(FieldName as String)
    Dim rs as DAO.RecordSet

    Set rs = CurrentDB.OpenRecordset("SELECT IDField FROM tblMyTable WHERE " & FieldName & " Is Null")

    If rs.eof then exit sub

    Do until rs.Eof
        debug.print rs!IDField
        rs.movenext
    Loop
End Sub

This should in code lookup all records where FieldName is Null and then print the results to the immediate window where you pass the FieldName as a string.

edited Mar 8, 2013 at 14:38

answered Mar 8, 2013 at 14:20

Mike

2322 gold badges3 silver badges11 bronze badges

Collectives™ on Stack Overflow

Testing if fields are null ms access

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related