Using COUNT in Access 2002

[April]

Seems like this should be simple but I just plain can't find a way to do it.
Does anybody know how I can count the number of times that the word "BLANK"
appears in a table? The table has over 100 fields with long field names and
I sure don't want to write some If statement for every single one of them.

I tried using the cross-tab query wizard but it seems that only counts ONE
field, not all the fields in the row.

If the wizards in access can't do this, is there some way I can write a bit
of Visual basic that will cycle through each field in each record and add to
a counter? I can't find a way to indicate row and field in visual basic.
Arrays are not my thing.

thanks in advance for any assistance you can provide.

 

[John W. Vinson]

Seems like this should be simple but I just plain can't find a way to do it.
Does anybody know how I can count the number of times that the word "BLANK"
appears in a table? The table has over 100 fields with long field names and
I sure don't want to write some If statement for every single one of them.

I tried using the cross-tab query wizard but it seems that only counts ONE
field, not all the fields in the row.

If the wizards in access can't do this, is there some way I can write a bit
of Visual basic that will cycle through each field in each record and add to
a counter? I can't find a way to indicate row and field in visual basic.
Arrays are not my thing.

thanks in advance for any assistance you can provide.

The table is clearly not properly normalized - which is why it's not an easy
query to build! You will indeed need to either create a monstrous query with a
criterion of

=BLANK

or, if you want to find the records where the field contains the word BLANK
along with other text (beware of Mr. Blankenship and the like!)

LIKE "*BLANK*"

on each and every field.

Assuming the former, this (horribly inefficient) VBA recordset code might get
you the desired count. This also assumes that all fields are Text and that all
fields are fair game for the count:

Public Function CountBlank() As Long
Dim rs As DAO.Recordset
Dim fld As DAO.Field
Dim db As DAO.Database
Dim fldcount As Integer
Dim i As Integer
CountBlank = 0
Set db = CurrentDb
Set rs = db.OpenRecordset("tablename", dbOpenDynaset)
fldcount = rs.Fields.Count
Do Until rs.EOF
For i = 0 to fldcount-1
If rs.Fields(i) = "BLANK" Then
CountBlank = CountBlank + 1
End If
Next i
Loop
End Function

 

[April]

Oh yes, I agree the table is a mess. I'm actually importing the table from
another database which I have no control over.

Thank you so much for the suggested code. I will see if I can work with
that.

 

[John Spencer]

This code should be a bit more efficient than John Vinson's

Public Function CountBlank(strTableName as String) As Long
Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim fldcount As Long
Dim i As Integer
dim fldName as String

CountBlank = 0
Set db = CurrentDb()
Set rs = db.OpenRecordset(strTableName, dbOpenDynaset)
fldcount = rs.Fields.Count

For i = 0 to fldCount-1
fldName = rs.Fields(i).Name
If rs.fields(i).Type = dbText then
CountBlank = CountBlank = DCount(FldName,"TableName",FldName &
"='Blank'")
End if
Next i
Debug.print CountBlank

End Function

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================