Trouble with Expression

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

--
Why don't my grey cells communicate with each as fast as they used to? I hate
getting old!

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/201002/1

Tony -

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


"TonyWilliams via AccessMonster.com" wrote:

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

--
Why don't my grey cells communicate with each as fast as they used to? I hate
getting old!

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/201002/1

.

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))

--
Build a little, test a little.


"TonyWilliams via AccessMonster.com" wrote:

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

--
Why don't my grey cells communicate with each as fast as they used to? I hate
getting old!

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/201002/1

.

On Fri, 19 Feb 2010 16:27:26 GMT, "TonyWilliams via AccessMonster.com"
u56994@uwe wrote:

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 W. Vinson [MVP]

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

KARL DEWEY wrote:
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))

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

TonyWilliams wrote:
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


--
Why don't my grey cells communicate with each as fast as they used to? I hate
getting old!

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/201002/1