Trouble with Expression

[TonyWilliams via AccessMonster.com]

This expression counts the number of records where a tick box is YES
TxtSoleYes: Count(IIf(txtSole,1,Null))
Why doesn't this work to give me the number of No's
TxtSoleYes: Count(IIf(txtSole,0,Null))
Thanks
Tony

 

[Daryl S]

Tony -

Try Sum instead of Count. Count gives the number of non-nulls, and zero is
non-null.

 

[KARL DEWEY]

TxtSoleYes: Count(IIf(txtSole,1,Null))
This is saying if txtSole is True then count it.

TxtSoleYes: Count(IIf(txtSole,0,Null))
This is saying the same thing.

Best to do full -- TxtSoleYes: Sum(IIf(txtSole = -1, 1, 0))
This says if txtSole = -1, which is the representation of True, then
return a 1, then sum them.

For No's use this -- TxtSoleNo: Sum(IIf(txtSole = 0, 1, 0))

 

[John W. Vinson]

This expression counts the number of records where a tick box is YES
TxtSoleYes: Count(IIf(txtSole,1,Null))
Why doesn't this work to give me the number of No's
TxtSoleYes: Count(IIf(txtSole,0,Null))
Thanks
Tony

Because it's returning NULL if txtSole is false, and the Count function counts
only non-NULL values.

Rather than using Count, use Sum:

TxtSoleYes: Sum(IIF(txtSole, 1, 0))
TxtSoleNo: Sum(IIF(txtSole, 0, 1))

Or if you wish, you can use the fact that a Yes/No field is stored as -1 for
Yes, 0 for No:

TxtSoleYes: Sum(Abs(txtSole))
TxtSoleNo: Sum(txtSole + 1)

 

[John Spencer]

txtSoleYes: Count(IIf(txtSole,1,Null))
txtSoleNo: Count(IIf(txtSole,Null,1))

Count counts the existence of a value other than null. 1 or 0 or "A" are all
values. Your IIF was always returning a value (1 or zero) if txtSole was True
and a Null if txtSole was false. Flipping the expression as above returned 1
if txtSole was false and Null if txtsole was true.
You could, of course, have used
txtSoleNo: Count(IIF(txtSole=False,1,Null))
and gotten the correct result.

You could use SUM expressions also.
txtSoleYes: Abs(Sum(txtSole))

txtSoleNo: Sum(txtSole+1)
Or
txtSoleNo: Sum(1-Abs(txtSole))


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[TonyWilliams via AccessMonster.com]

Thanks everyone so much choice!
Really appreciate your help.
Tony