If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Trouble with Expression
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 . |
#3
|
|||
|
|||
Trouble with Expression
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 . |
#4
|
|||
|
|||
Trouble with Expression
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] |
#5
|
|||
|
|||
Trouble with Expression
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)) |
#6
|
|||
|
|||
Trouble with Expression
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 |
Thread Tools | |
Display Modes | |
|
|