CODEWITHSUNDEEP
vbams-access
Katana24
Katana24

Reputation: 8959

Testing if fields are null ms access

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

Upvotes: 3

Views: 45761

Answers (3)

Mike
Mike

Reputation: 232

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.

Upvotes: 1

HansUp
HansUp

Reputation: 97101

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.

Upvotes: 2

user2088176
user2088176

Reputation: 246

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

Upvotes: 2

Related Questions