Count VB

  • Thread starter Thread starter EMH
  • Start date Start date
E

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
 
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
 
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>




.
Click to expand...
 
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
 
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?
 
Back
Top