Count Problem

[Karl Krelove]

I have a table that contains Student ID numbers and Yes/No fields that
indicate whether or not each attended each of several class meetings. So
there's an ID field and 6 class fields (Class1, Class2, etc.) - probably more
later when I've figured out how to do this. I want to show in a form each of
these fields for each student (along with names and some other information
from another table related to this through the ID numbers). At the end of
each row I want to show how many classes each student attended. At the bottom
of each column I want to show how many students attended each class. I've
built a query simply to base the form on. When I try to use Count() at the
bottom of the columns, I get the total number of records, since each has a
Yes or No property (I assume Yes/No cannot be NULL, which is apparently the
only thing Count() won't count). I haven't found a built in function that
seems to be able to add the Yes fields across.

Am I missing a simple way to do this? I'm starting to work with VBA. I can
loop through each field in each record, compare the value to "Yes" and
increment a counter each time one is found? I guess I could do the same for
the column counts as well. It just seems like a cumbersome way to do it. Are
there simpler solutions?

TIA

Karl Krelove

 

[CompGeek78]

Look into the DCount function instead of the Count function. DCount
lets you specify to count a field in a table given a criteria.

Yours would be dcount([fieldname],[tablename],"yes")

 

[Karl Krelove]

Thanks. I looked at that and for some reason skipped it. This will handle the
column count, which is based on counting a single field for all records. I
may still need something else to count across the several Boolean fields for
each record, but I think I can work out a solution using Count for that.

Karl

 

[Karl Krelove]

OK - 2 things.

Turns out all I need to do about the row totals is add the Yes/No fields and
use the absolute value. Yes (True) evaluates as -1 and No as 0. I found it
odd though - when I first tried it I used the Sum() function and found that
each field evaluated as -14 regardless of its Yes/No state. Do you know why
that happens? Sum(F1+F2+F3+F4) becomes -56. When I eliminate Sum and just add
the fields' values - =Abs(F1+F2+F3+F4) - I get a correct count of Yes values
among the four fields.

As to DCount, I'm having trouble with the [tablename] value. I'm in the
Expression builder. If I use the fieldname from the form's field list in the
rightmost column of the Expression Builder and the name of the form as the
[tablename], I get a #Name? error in the form display. If I leave the form
name out, with or without leaving the comma in the expression, I get an error
from the Expression Builder when I try to leave it. If I try to use the
source table in which the data is actually stored or the query on which the
form is based, I get an error saying the object (I assume the form) doesn't
contain the Automation object (the table). Can you break this down any more -
and should I be using the Expression Builder for this?

Thanks.

 

[Kevin]

Hi Karl,

Try putting quotes around the brackets for fieldname and tablename.

Hope that works.

Kevin