Syntax for total text box in footer?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

CW - 07 Mar 2008 13:36 GMT

I have a report that shows the results of our customer satisfaction
questionnaires.
One of the fields is "Recommend" and the value is a Y/N checkbox.
I want to create a textbox in the report footer that counts the number of
entries where the Recommend value is Y.
I have tried entering this as the control source:
=Count([Recommend]="Y")
which I thought seemed a reasonable attempt, but no good.
Would be very grateful for your advice
Many thanks
CW

Reply to this Message

John Spencer - 07 Mar 2008 13:49 GMT

Try
=Abs(Sum([Recommend]))

How it works
A Check box has a value of 0 (unchecked) or -1 (Checked). So we sum the
values and get a negative number for all the boxes that are checked. We use
the Abs function (absolute value) to get rid of the negative sign.

Count counts the presence of a value. The only time Count does not count
something is if the value is NULL. Since 0 and -1 are both values you get a
count of the number of checkboxes. You could count the checks with this
statement

=Count(IIF([Recommend] = True, 1,Null))
amd the non-checks with
=Count(IIF([Recommend] = False, 1,Null))

Signature

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
.

>I have a report that shows the results of our customer satisfaction
> questionnaires.
[quoted text clipped - 7 lines]
> Many thanks
> CW

Reply to this Message

CW - 07 Mar 2008 14:01 GMT

Perfect! Worked first time. And I appreciate the explanation of how it works.
Many thanks John.
CW

> Try
> =Abs(Sum([Recommend]))
[quoted text clipped - 24 lines]
> > Many thanks
> > CW

Reply to this Message