count for true in report

  • Thread starter Thread starter Chris Wagner
  • Start date Start date
C

Chris Wagner

Access 2000 win 98

I have a table that has yes/no fields

in doing a report I am trying to get a count of those checked yes.

I have tried several different formulas in the footer but to no avail.
any suggestions

Thanks
Chris Wagner
 
Chris Wagner said:
Access 2000 win 98

I have a table that has yes/no fields

in doing a report I am trying to get a count of those checked yes.

I have tried several different formulas in the footer but to no avail.
any suggestions

Thanks
Chris Wagner
Click to expand...

Take the absolute value of the sum of the field:

=Abs(Sum([MyYesNoField]))
 
Thanks for the reply.

I did find a way for this one instance that was to turn the 'running sum'
to 'over all' and the formula to =Sum([MyYesNoField])*-1.

Any idea why I would get a negative number without the *-1. I noticed you
put in the ABS which would do the same thing in this case.

Also I would get a total number of records without the 'running sum'
selected. does this appear to be a true statement?

Thanks

Chris Wagner

Dirk said:
Chris Wagner said:
Access 2000 win 98

I have a table that has yes/no fields

in doing a report I am trying to get a count of those checked yes.

I have tried several different formulas in the footer but to no avail.
any suggestions

Thanks
Chris Wagner
Click to expand...

Take the absolute value of the sum of the field:

=Abs(Sum([MyYesNoField]))

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Chris Wagner said:
Thanks for the reply.

I did find a way for this one instance that was to turn the
'running sum' to 'over all' and the formula to
=Sum([MyYesNoField])*-1.

Any idea why I would get a negative number without the *-1. I
noticed you put in the ABS which would do the same thing in this
case.
Click to expand...

Yes, that's based on the same principle, as is

=-Sum([MyYesNoField])

The reason this works is that the Jet database engine used by Access
stores Boolean (Yes/No) fields as integers with a value of -1 for True
and 0 for False. So to count all the "yeses" in a field you just sum
the field and take the negative or absolute value. I use Abs() instead
of the simple negative to guard against my application being run with
some different data store that uses 1 for True instead of -1. For even
greater safety, one could use this expression instead:

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

So far, I've never heard of any boolean representation that didn't use 0
to = False.
Also I would get a total number of records without the 'running sum'
selected. does this appear to be a true statement?
Click to expand...

I don't understand the question. If you put one of these expressions in
the controlsource of a text box in the report footer, you don't need to
use the RunningSum property, if that's what you mean.
 
I'll give it a try

Thanks Again
Chris Wagner

Dirk said:
Chris Wagner said:
Thanks for the reply.

I did find a way for this one instance that was to turn the
'running sum' to 'over all' and the formula to
=Sum([MyYesNoField])*-1.

Any idea why I would get a negative number without the *-1. I
noticed you put in the ABS which would do the same thing in this
case.
Click to expand...

Yes, that's based on the same principle, as is

=-Sum([MyYesNoField])

The reason this works is that the Jet database engine used by Access
stores Boolean (Yes/No) fields as integers with a value of -1 for True
and 0 for False. So to count all the "yeses" in a field you just sum
the field and take the negative or absolute value. I use Abs() instead
of the simple negative to guard against my application being run with
some different data store that uses 1 for True instead of -1. For even
greater safety, one could use this expression instead:

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

So far, I've never heard of any boolean representation that didn't use 0
to = False.
Also I would get a total number of records without the 'running sum'
selected. does this appear to be a true statement?
Click to expand...

I don't understand the question. If you put one of these expressions in
the controlsource of a text box in the report footer, you don't need to
use the RunningSum property, if that's what you mean.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Back
Top