Results 1 to 4 of 4
  1. #1
    MsAxes is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2021
    Posts
    100

    How to identify missing data values?

    Hi all



    I have a database with 3 tables.
    • Field_Req - This table identifies which fields in the Data table require/don't require data.
    • Data - This table has about 10k records that gets updated daily (100 columns).
    • Results - This table holds the results of which fields have data but shouldn't and vice-versa.



    I also have the VBA below, its goal is to:
    • Go through the Data and Field_Req table to identify records that are missing required data or have data but shouldn't. I run the code and for some reason it only loops through one record instead of the whole data set (it identifies which records are missing data or vice-versa correctly). I don't see why it stops on one record when I know fields that don't require data have a value.



    Code:
    Public Sub CheckFields()    Dim rsFieldReq As DAO.Recordset
        Dim rsData As DAO.Recordset
        Dim rsResults As DAO.Recordset
        Dim fld As DAO.Field
        Dim strFieldName As String
        Dim strAct As String
        
        ' Open recordsets for Field_Req, Data, and Results tables
        Set rsFieldReq = CurrentDb.OpenRecordset("Field_Req")
        Set rsData = CurrentDb.OpenRecordset("Data")
        Set rsResults = CurrentDb.OpenRecordset("Results")
        
        ' Loop through each field in Field_Req table
        For Each fld In rsFieldReq.Fields
            ' Check if field is required (Y) or not (N)
            If fld.Value = "Y" Then
                ' Field is required, check if it is null in Data table
                If IsNull(rsData(fld.Name)) Then
                    ' Field is null, add error to Results table
                    strFieldName = fld.Name
                    strAct = rsData("act num").Value
                    rsResults.AddNew
                    rsResults("act num").Value = strAct
                    rsResults("FieldName").Value = strFieldName
                    rsResults("ErrorType").Value = "Y-field is null"
                    rsResults.Update
                End If
            ElseIf fld.Value = "N" Then
                ' Field is not required, check if it is not null in Data table
                If Not IsNull(rsData(fld.Name)) Then
                    ' Field is not null, add error to Results table
                    strFieldName = fld.Name
                    strAct = rsData("act num").Value
                    rsResults.AddNew
                    rsResults("act num").Value = strAct
                    rsResults("FieldName").Value = strFieldName
                    rsResults("ErrorType").Value = "N-field is not null"
                    rsResults.Update
                End If
            End If
        Next fld
        
        ' Close recordsets
        rsFieldReq.Close
        rsData.Close
        rsResults.Close
        Set rsFieldReq = Nothing
        Set rsData = Nothing
        Set rsResults = Nothing
    End Sub




    I'll appreciate any feed-back.

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Well you do not appear to be moving to another record?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    MsAxes is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2021
    Posts
    100
    Ah! That would help right?

    Thank you Welshgasman.

  4. #4
    MsAxes is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2021
    Posts
    100
    Quote Originally Posted by MsAxes View Post
    Ah! That would help right?

    Thank you Welshgasman.
    Here is the revised code in case anyone needs it in the future.

    Code:
    Public Sub CheckFields()    Dim rsFieldReq As DAO.Recordset    Dim rsFieldReq As DAO.Recordset
        Dim rsData As DAO.Recordset
        Dim rsResults As DAO.Recordset
        Dim fld As DAO.Field
        Dim strFieldName As String
        Dim strAct As String
        
        ' Open recordsets for Field_Req, Data, and Results tables
        Set rsFieldReq = CurrentDb.OpenRecordset("Field_Req")
        Set rsData = CurrentDb.OpenRecordset("Data")
        Set rsResults = CurrentDb.OpenRecordset("Results")
        
        ' Loop through each record in Data table
        Do While Not rsData.EOF
            ' Loop through each field in Field_Req table
            For Each fld In rsFieldReq.Fields
             ' Check if field is required (Y) or not (N)
                If fld.Value = "Y" Then
                 ' Field is required, check if it is null in Data table
                 If IsNull(rsData(fld.Name)) Then
                    ' Field is null, add error to Results table
                    strFieldName = fld.Name
                    strAct = rsData("act num").Value
                    rsResults.AddNew
                    rsResults("act num").Value = strAct
                    rsResults("FieldName").Value = strFieldName
                    rsResults("ErrorType").Value = "Y-field is null"
                    rsResults.Update
                End If
            ElseIf fld.Value = "N" Then
                ' Field is not required, check if it is not null in Data table
                If Not IsNull(rsData(fld.Name)) Then
                    ' Field is not null, add error to Results table
                    strFieldName = fld.Name
                    strAct = rsData("act num").Value
                    rsResults.AddNew
                    rsResults("act num").Value = strAct
                    rsResults("FieldName").Value = strFieldName
                    rsResults("ErrorType").Value = "N-field is not null"
                    rsResults.Update
                End If
            End If
        Next fld
        
         rsData.MoveNext ' Move to the next record in Data table
        Loop
        
        ' Close recordsets
        rsFieldReq.Close
        rsData.Close
        rsResults.Close
        Set rsFieldReq = Nothing
        Set rsData = Nothing
        Set rsResults = Nothing
    End Sub

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 03-03-2023, 03:21 PM
  2. Need query to identify 10-digit numeric values
    By skydivetom in forum Queries
    Replies: 5
    Last Post: 06-19-2021, 06:21 AM
  3. Replies: 6
    Last Post: 09-12-2019, 12:23 PM
  4. How to handle missing values when cleaning data?
    By TerraEarth in forum Programming
    Replies: 1
    Last Post: 04-10-2018, 05:25 PM
  5. Importing Data with missing values
    By tcheck in forum Access
    Replies: 1
    Last Post: 02-04-2014, 03:28 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums