IFF statement not working (1 Viewer)

[rainbows]

the data below shows me what i have invoiced and what has been paid
I am trying to return the net amount only if the invoice has been paid
the 2 ways i have tried either give me all the same results ie net amount and exp1 even if the paid has not been ticked


Expr1: IIf(Not IsNull([paid]),[Net Amount],0)
Expr2: IIf([paid]="YES",[NET AMOUNT],0)

thanks
steve


find invoice find invoice

Net Amount	Expr1	Paid
12740​	12740​	Yes​
8200​	8200​	No​
1080​	1080​	Yes​
1080​	1080​	Yes​
3240​	3240​	Yes​
540​	540​	No​
1755​	1755​	Yes​
5800​	5800​	Yes​
4170​	4170​	No​

 

[Minty]

The first one won't work as Paid appears to always have a value, so is never Null.

The second one should work, but the results aren't in your post, so can't work out what is happening there.
Is [Paid] a calculated column?

 

[rainbows]

Expr2
​
​
​
​
​
​
​
​
​

the second one just comes up with the word error . the paid column is just a tick box yes/no

 

[Gasman]

Expr2
​
​
​
​
​
​
​
​
​

the second one just comes up with the word error . the paid column is just a tick box yes/no

So would be True or False?

 

[Josef P.]

the paid column is just a tick box yes/no

=> compare with True/False and not with a string.
Expr2: IIf([paid]=True,[NET AMOUNT],0)

 

[rainbows]

Thank you

Expr2: IIf([paid]=True,[NET AMOUNT],0)

works ok

steve

 

[Pat Hartman]

Check boxes are Y/N fields. They contain a numeric value. 0 = False. -1 = True. They are displayed as "True"/"False", "Yes"/"No" or as a checkbox. The value is NOT a string. So you have to use True or False which are constants that = -1 or 0, rather than "Yes" or "No" or "True" or "False" which are strings.

 

[CJ_London]

All you actually need is

Expr2: IIf([paid],[NET AMOUNT],0)

or as an alternative calculation

Expr2: abs([paid]*[NET AMOUNT])