Count non null fields

[FrontSlider]

I have a query that has 141 fields. A field may be populated or empty. I am trying to count the number of fields that are not null using vba.

dCount only give me the number of rows
FieldCount = DCount("*", "RawIssuesHeadings03")

 

[jdraw]

Can you tell us more about your application, your table setup and your "business need"?

141 fields is not very common
and counting NULLs in individual fields is not a very common request.

Perhaps some restructuring may help???

 

[FrontSlider]

The query identifies if any of the imported table field headings have changed. The query will only have one row with changed headings showing up and null contents if no change was made across all 141 fields. If 5 headings have changed all I need to do is count the number of fields in the query that are not null, ie populated. The vba code should give me a value of 5 in this case.

 

[FrontSlider]

For 2 days I have only been able to find code that tells me the number of rows in my query. It seems counting rows is easy but counting fields is not?

 

[ghudson]

With a simple google searched I found plenty of options >>> access vba count fields in table

I prefer this option...

CurrentDb.TableDefs("MyTableName").Fields.Count

 

[FrontSlider]

This is where I pasted in your code. Nothing happens, it doesn't count.

Function CountRawIssuesHeadings()
changes = CurrentDb.TableDefs("RawIssuesHeadings03").Fields.Count
If changes > 0 Then
MsgBox "You have " & changes & " changed heading(s) or they are not in the correct order. Click OK, update your headings and Import again."
Else
End If
End Function

 

[jdraw]

I think ghudson was giving you a means to count the number of fields in a table.
The code has nothing to do with field values.

I'm not clear on what exactly you are doing.
Do you have 1 record involved and you want to know how many fields in that records are NOT NULL?

This

with changed headings showing up and null contents if no change was made across all 141 fields

is obviously relevant to your question but please explain.

Please post your query sql.

 

[FrontSlider]

Yes, I have one record and only need to know how many fields on 141 fields are populated.

 

[jdraw]

I tested this against a table with known null values.
This table had 6 records. Each record has 8 fields.
21 fields had NOT null values. You'll have to use your Table name as shown.

Sub testfldprintvalue()
     Dim dbs As DAO.Database
     Dim rst As DAO.Recordset
     Dim tdf As TableDef
     Dim fld As Field
     Dim Changes As Integer
     Dim strtable As String
   On Error GoTo fjTdfRst_Error
      strtable = "somecsv"  '[COLOR="SeaGreen"][B]<<<Your tablename goes here[/B][/COLOR] 
     
     Set dbs = CurrentDb()
     Set rst = dbs.OpenRecordset(strtable)    'open the recordset  (data)
     Set tdf = dbs.TableDefs(strtable)        'open the tabledef  (definition)
        
     While Not rst.EOF       'loop through the recordset
        
         For Each fld In tdf.Fields           'loop through the fields
           Debug.Print fld.name & ": " & rst(fld.name).Value 'print field name and value
         If NOT IsNull(rst(fld.name).Value) Then
         Changes = Changes + 1
         Else
         End If
         Next fld
         rst.MoveNext
     Wend
End_loop:
  MsgBox "Total non null fields is " & Changes
rst.Close
    
   On Error GoTo 0
   Exit Sub

fjTdfRst_Error:
    If Err.number = 3021 Then
       MsgBox ("Error # " & str(Err.number) & " implies no data in table " & rst.name)
    End If
    MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure "
End Sub

I hope this is useful to you.

 

[FrontSlider]

I have a feeling it's almost there. It crashes on "For Each fld In db.TableDefs("RawIssuesHeadings03").Fields" with a 3219 error message

 

[jdraw]

See my last post, I had to edit it. I need to have the table definition and the recordset open to list each field name and value.
I tested the code to see that it would work.

 

[FrontSlider]

Jdraw that works perfectly on a table. I guess now all I need to do is change the table references to query references and test it. If that doesn't work then I'll just change the query into a make table query and reference the new table. Thanks for all the help. You're the greatest!

 

[jdraw]

I just realized you said query and I never did see the query.
I can make the code work for query as well
Querydefs have Fields

Sub testfldprintvalue()
     Dim dbs As DAO.Database
     Dim rst As DAO.Recordset
     Dim tdf As QueryDef    ' [COLOR="SeaGreen"][B]identify this as querydef instead of Tabledef[/B][/COLOR]
     Dim fld As Field
     Dim Changes As Integer
     Dim strtable As String
   On Error GoTo fjTdfRst_Error
      strtable = "qsomecsv"
     
     Set dbs = CurrentDb()
     Set rst = dbs.OpenRecordset(strtable)    'open the recordset  (data)
     Set tdf = dbs.QueryDefs(strtable)        '[COLOR="SeaGreen"][B]open the querydef  (definition)[/B[/COLOR]]
        
     While Not rst.EOF       'loop through the recordset
        
         For Each fld In tdf.Fields           '[COLOR="SeaGreen"][B]loop through the fields in query def[/B][/COLOR]
           Debug.Print fld.name & ": " & rst(fld.name).Value 'print field name and value
         If Not IsNull(rst(fld.name).Value) Then
         Changes = Changes + 1
         Else
         End If
         Next fld
         rst.MoveNext
     Wend
End_loop:
  MsgBox "Total null fields is " & Changes
rst.Close
    
   On Error GoTo 0
   Exit Sub

fjTdfRst_Error:
    If Err.number = 3021 Then
       MsgBox ("Error # " & str(Err.number) & " implies no data in table " & rst.name)
    End If
    MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure "
End Sub

 

[FrontSlider]

Thanks Jdraw, I already modified your code to account for queries rather than tables. I had a little problem with using double quotes in my query that I had to change to null in order for them to not be counted. Thanks again for all of the help, I am extremely grateful!