Yes/No records

[Joan]

I have several fields that have yes/no and would like to run a report
that shows just the total of "yes" for each field. Some of the records
have yes in more than one field. Any suggestions?

 

[Guest]

You can count the Boolean (Yes/No) fields which are True (Yes) by summing an
expression using the IIf function to return 1 or 0. In the report footer put
text boxes for each fieled with a ControlSource such as:

=Sum(IIf([FieldName],1,0))

where FieldName is the name of the Boolean field in question.

You might see it recommended in some books that you use an expression such as:

=Sum(Abs([FieldName]))

or:

=Sum([FieldName] * -1)

Ignore this advice! It relies on the implementation in Access of Boolean
value as -1 (True) and 0 (False). Relying on the implementation is bad
programming practice.

Ken Sheridan
Stafford, England

 

[fredg]

I have several fields that have yes/no and would like to run a report
that shows just the total of "yes" for each field. Some of the records
have yes in more than one field. Any suggestions?

Add an unbound control to your report.
Set it's Control source to:

=ABS(Sum([CheckBoxfieldName]))

Repeat for each additional check box field.

 

[Guest]

I have to disagree with you on this point, Ken. -1 has been true and 0 false
for as long as there has been Access. I see no reason not to use that
construct. It will, in fact, be as reliable as your recommendation and
faster to execute because the IIf will not have to be evaluated.

You can count the Boolean (Yes/No) fields which are True (Yes) by summing an
expression using the IIf function to return 1 or 0. In the report footer put
text boxes for each fieled with a ControlSource such as:

=Sum(IIf([FieldName],1,0))

where FieldName is the name of the Boolean field in question.

You might see it recommended in some books that you use an expression such as:

=Sum(Abs([FieldName]))

or:

=Sum([FieldName] * -1)

Ignore this advice! It relies on the implementation in Access of Boolean
value as -1 (True) and 0 (False). Relying on the implementation is bad
programming practice.

Ken Sheridan
Stafford, England

I have several fields that have yes/no and would like to run a report
that shows just the total of "yes" for each field. Some of the records
have yes in more than one field. Any suggestions?

 

[Joan]

I did try this, but when the report prints, it's trying to group things
together rather than give me a total for each at the bottom. Any ideas
what I'm doing wrong?

 

[Guest]

You are of course free to do as you wish, but I maintain my view that it is
bad practice. Its what the head of one software development company with
whom I used to have contact described as being 'unduly chummy with the
implementatiion' and goes against one of the fundamental principles of best
programming practice.

I merely make the point. Its for others to accept or reject as they wish.
That's no skin off my nose.

Ken Sheridan
Stafford, England

I have to disagree with you on this point, Ken. -1 has been true and 0 false
for as long as there has been Access. I see no reason not to use that
construct. It will, in fact, be as reliable as your recommendation and
faster to execute because the IIf will not have to be evaluated.

You can count the Boolean (Yes/No) fields which are True (Yes) by summing an
expression using the IIf function to return 1 or 0. In the report footer put
text boxes for each fieled with a ControlSource such as:

=Sum(IIf([FieldName],1,0))

where FieldName is the name of the Boolean field in question.

You might see it recommended in some books that you use an expression such as:

=Sum(Abs([FieldName]))

or:

=Sum([FieldName] * -1)

Ignore this advice! It relies on the implementation in Access of Boolean
value as -1 (True) and 0 (False). Relying on the implementation is bad
programming practice.

Ken Sheridan
Stafford, England

I have several fields that have yes/no and would like to run a report
that shows just the total of "yes" for each field. Some of the records
have yes in more than one field. Any suggestions?

 

[Guest]

Whatever form of expression you use the controls should be in the report
footer. That way there can only be one set of them. It sounds like you
might have them in a group footer.

You'll have seen my views over the reliance on the implementation which
Fred's expression exploits. Its for you to decide which approach to use.

Another way you can do this is by counting the rows in the table or query
directly with the DSum function, so the ControlSource for a text box to show
how many rows there are where a Boolean fieldis True would be:

=DCount("*","MyTable", "MyField = True")

Unlike using the Sum function this control could be anywhere in the report.
In fact if you only wanted to show totals and no detail at all the report
would not even need to be based on a table or query.

You can also do it in a query:

SELECT
SUM(IIF(Field1= TRUE,1,0)) As CountOfField1,
SUM(IIF(Field2= TRUE,1,0)) As CountOfField2,
SUM(IIF(Field3= TRUE,1,0)) As CountOfField3
FROM MyTable;

Ken Sheridan
Stafford, England

 

[Joan]

THANK YOU!!! That was the problem - Once I moved them to the report
footer it worked perfectly.