Count VB

[EMH]

I have a table that has blank fields in it for any record
item that is the same.

If a person has more than one class asigned to them the
table does not have data for the fields that are the
same. so if the only differnece between line 1 and 2 is
that the clas name is different then the admit date, name,
address, city, state, zip fields (same for line 1 and 2)
are blank on line 2 and the only field with data is the
class field.

this occurs for all students with more than one classs
assigned. I have written a function that I am trying to
use to count the number of blank lines for each student.

If james, david has 3 clases the admit date field is blank
2 times. If Smith, Bob has 5 classes the admit date field
is blank 4 times.

Here is the code for the function that is returning 0 for
all counts.

Function CountBlanks(FiledName As String, sourcename As
String)
Dim db As DAO.Database, rs As DAO.Recordset, LineCount
As Integer

Set db = CurrentDb
Set rs = db.OpenRecordset(sourcename, dbOpenDynaset)
rs.MoveFirst
LineCount = 0
Do While FiledName Is Null
LineCount = LineCount + 1
Loop

CountBlanks = LineCount


End Function

Any help you could give would be extemely usefull.

Mark

 

[Ken Snell [MVP]]

Your code doesn't loop through the field names, which is what I assume you
want to do (I also assume that you have only one record in the recordset).
You also aren't properly getting the field's value. Try this:

Function CountBlanks(FiledName As String, sourcename As String)
Dim db As DAO.Database, rs As DAO.Recordset, LineCount As Integer
Dim fld As DAO.Field
Set db = CurrentDb
Set rs = db.OpenRecordset(sourcename, dbOpenDynaset)
rs.MoveFirst
LineCount = 0
For Each fld In rst.Fields
If fld.Value Is Null Then LineCount = LineCount + 1
Next fld
CountBlanks = LineCount
End Function

 

[Guest]

I get a runtime error of 424. Any thoughts?

-----Original Message-----
Your code doesn't loop through the field names, which is what I assume you
want to do (I also assume that you have only one record in the recordset).
You also aren't properly getting the field's value. Try this:

Function CountBlanks(FiledName As String, sourcename As String)
Dim db As DAO.Database, rs As DAO.Recordset, LineCount As Integer
Dim fld As DAO.Field
Set db = CurrentDb
Set rs = db.OpenRecordset(sourcename, dbOpenDynaset)
rs.MoveFirst
LineCount = 0
For Each fld In rst.Fields
If fld.Value Is Null Then LineCount = LineCount + 1
Next fld
CountBlanks = LineCount
End Function

--

Ken Snell
<MS ACCESS MVP>




.

 

[Ken Snell [MVP]]

Sorry... I mistyped rs as rst in the For Each line -- try this:

Function CountBlanks(FiledName As String, sourcename As String)
Dim db As DAO.Database, rs As DAO.Recordset, LineCount As Integer
Dim fld As DAO.Field
Set db = CurrentDb
Set rs = db.OpenRecordset(sourcename, dbOpenDynaset)
rs.MoveFirst
LineCount = 0
For Each fld In rs.Fields
If fld.Value Is Null Then LineCount = LineCount + 1
Next fld
CountBlanks = LineCount
End Function

 

[emh]

I still get that error. I had caught the mistype. when I
go into the debugger The highlight is on If fld.Value Is
Null Then

Sorry to keep buggin you on this, but thoughts on this?

 

[Douglas J. Steele]

Try

If IsNull(fld.Value) Then LineCount = LineCount +

 

[Ken Snell [MVP]]

Thanks, Doug.

--

Ken Snell
<MS ACCESS MVP>

Try

If IsNull(fld.Value) Then LineCount = LineCount +