Results 1 to 8 of 8
  1. #1
    ibnumarjan@gmail.com is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2022
    Posts
    4

    Lightbulb Find all columns/fields (50 fields) with null values for every record based ID field

    Thanks for accepting my registartion in here.




    Can anyone help me to filter the records where all column/field (50 fields) values are null and list the particular IDs. here I have shown only 4 columns and 4 rows (sample) and I am expecting the out put only ID=3 & 5 where all the columns (filed1 ..field50) are empty or Null, others might have data for even one column which I don't want to show.

    ID(PK) field1 field2... filed25... filed50
    1 ABC PQR
    2 LMN RPK TQR STV
    3
    4 TPO
    5











    I got the following code from another forum, but it is giving just a blank record in when I call the function in query

    Code:
    Public Function CheckNulls(ByVal Id As Long) As Boolean
    
    
        Dim Records As DAO.Recordset
        Dim Field   As DAO.Field
        
        Dim AllNull As Boolean
        
        Set Records = CurrentDb.OpenRecordset("Select * From tbltest Where Id = " & Id & "")
        
        If Records.RecordCount = 1 Then
            For Each Field In Records.Fields
                If Field.Name = "Id" Then
                    Set Field = Nothing
                ElseIf Not IsNull(Field.Value) Then
                    Exit For
                ' Check for Zero length string:
                ElseIf Not Nz(Field.Value) <> "" Then
                    Exit For
                End If
            Next
        End If
        Records.Close
        
        AllNull = (Field Is Nothing)
        
        CheckNulls = AllNull
        
    End Function
    Code:
    SELECT tbltest.Id, CheckNulls([Id]) AS a
    FROM tbltest
    WHERE (((CheckNulls([Id]))=True));

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Did you modify the code you found? Just remove Not from:

    ElseIf Not Nz(Field.Value) <> "" Then

    Consider following.
    Code:
    Public Function CheckNulls(ByVal ID As Long) As Boolean
        Dim Records As DAO.Recordset
        Dim Field   As DAO.Field
        
        Set Records = CurrentDb.OpenRecordset("Select * From tbltest Where Id = " & ID)
        CheckNulls = True
        For Each Field In Records.Fields
            If Field.Name <> "Id" Then
                If Field.Value & "" <> "" Then
                    CheckNulls = False
                    Exit For
                End If
            End If
        Next
        Records.Close
    End Function
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    NZ() only works for Nulls, not ZLS ?
    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

  4. #4
    ibnumarjan@gmail.com is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2022
    Posts
    4
    Thanks @June7,

    I tested with your code as above but same output.

    Can I attach my sample database here?

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Field is a reserved word and should not be used as a variable/object/table/field name.

    The function you have copied from another forum does not do what you want so presumably had another purpose

    try this alternative - note this is returns an integer because there are 3 possible responses based on your example code. If you want a boolean you need to take out the 'If Rst.RecordCount = 1' clause



    Code:
    Public Function CheckIfAllNulls(ByVal Id As Long) As integer
    
    
        Dim Rst As DAO.Recordset
        Dim Fld   As DAO.Field
        Dim AllNull As integer
        
        Set Rst = CurrentDb.OpenRecordset("Select * From tbltest Where Id = " & Id, dbfailonerror)
        
        AllNull=1
        If Rst.RecordCount = 1 Then 'only one record to test which is the requirement
            For Each Fld In Rst.Fields
                If Fld.Name <> "Id" Then
                   If nz(Fld.Value,"") <> "" Then 'this field as a value
                     AllNull=0  
                    Exit For
                end if
            Next fld
            allnull=-allnull ' i.e. if allnull=1 then allnull=-1
        End If
        Rst.Close
        
         CheckIfAllNulls = AllNull '-1 means all null, 0 means not all null, 1 means more than one record so not tested
        
    End Function
    and in your sql

    SELECT ID
    FROM tblTest
    WHERE CheckIfAllNulls([ID])=-1

  6. #6
    ibnumarjan@gmail.com is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2022
    Posts
    4
    Thanks a lot, it works now, there was type error in my side.

  7. #7
    ibnumarjan@gmail.com is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2022
    Posts
    4
    Super, Thanks a lot it works as well.

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Both code I posted worked for me.

    CJ's point about Field reserved word is reasonable, however, my code still worked.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 21
    Last Post: 08-28-2018, 02:53 PM
  2. Hide Report Fields based on values in each record
    By hfreedman1957 in forum Reports
    Replies: 4
    Last Post: 08-02-2017, 05:45 PM
  3. Replies: 3
    Last Post: 04-01-2015, 09:46 AM
  4. Replies: 1
    Last Post: 02-27-2015, 05:03 PM
  5. Replies: 1
    Last Post: 06-23-2014, 07:21 AM

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