Counting the number of "True" values in a boolean column.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am printing a report that has four fields: and ID, title, and two yes/no
columns. I want to count the number of times the first yes/no column is
"yes", and the number of times that BOTH yes/no columns are "yes".

I made two text boxes (one for each), and put the following in the control
source parameter (note: columns are named IsRequired and IsComplete):

=Count([IsRequired]=True)
=Count([IsComplete]=True And [IsRequired]=True)

All this does, though, is count the number of records being displayed on the
report. The data for IsRequired and IsComplete are displaying correctly, and
are part of the report's record source.

I tried using the Sum function (using the report wizard), but that doesn't
actually count anything, and simply displays a Yes or No (which makes sense,
I suppose).

From my basic understanding of expression building, those look right to me.
Any help would greatly be appreciated.

Thanks in advance.
 
Try

=Sum(Abs([IsRequired]=True))
=Sum(Abs([IsComplete]=True And [IsRequired]=True))

Or
=Sum(IIf([IsRequired]=True,1,0))
=Sum(IIf([IsComplete]=True And [IsRequired]=True,1,0))
 
I am printing a report that has four fields: and ID, title, and two yes/no
columns. I want to count the number of times the first yes/no column is
"yes", and the number of times that BOTH yes/no columns are "yes".

I made two text boxes (one for each), and put the following in the control
source parameter (note: columns are named IsRequired and IsComplete):

=Count([IsRequired]=True)
=Count([IsComplete]=True And [IsRequired]=True)

All this does, though, is count the number of records being displayed on the
report. The data for IsRequired and IsComplete are displaying correctly, and
are part of the report's record source.

I tried using the Sum function (using the report wizard), but that doesn't
actually count anything, and simply displays a Yes or No (which makes sense,
I suppose).

From my basic understanding of expression building, those look right to me.
Any help would greatly be appreciated.

Thanks in advance.
Click to expand...

In any report section (except the report's Page Footer), using unbound
text controls, to count [IsRequired]:
=Abs(Sum([IsRequired]))

To Count [IsComplete]:
=Abs(Sum([IsComplete]))

To count the total of both columns:
=Abs(Sum([IsRequired])) + Abs(Sum([IsComplete]))
 
Thank you! You're the best! I used the first suggestion. Can't believe I
didn't think of that.
 
Back
Top