IFF Statement Needs Fixing

[aamer]

TBALANCE: IIf([ToTBAL]>0 And Not Null,"RED","GREEN")

Please Help Me Fix This

[RuralGuy]

Try: TBALANCE: IIf([ToTBAL]>0 And Not IsNull([ToTbal]),"RED","GREEN")

[aamer]

TBALANCE: IIf([ToTBAL]>0 And Not IsNull([ToTbal]),"RED","GREEN")

This is not working
If Totbal is null, I am getting the answer GREEN, where as the answer should be null/Empty

[aamer]

Am Still Stuck With this Problem
Can Someone Help

TBALANCE: IIf([ToTBAL]>0 And Not IsNull([ToTbal]),"RED","GREEN")

This is not working
If Totbal is null, I am getting the answer GREEN, where as the answer should be null/Empty.

[June7]

Need to explicitly define rules.

When should the answer be RED?

When should the answer be GREEN?

When should the answer be Null?

[aamer]

When Value is Less than 0 - RED
When Value is More Than 0 - GREEN
When Value is Equal to 0 - The Answer Should Be Null or BLUE

[hapm]

Youwill need to cascade the ifs:

IIf(IsNull([ToTBAL]);Null;IIf([ToTBAL]>0;"GREEN";IIf([ToTBAL]<0;"RED";"Whatever you want if [ToTBAL]=0")))

Replace the last string with what ever output you want, when [ToTBAL] = 0.

[aamer]

I replaced null with "Whatever you want if"


IIf(IsNull([ToTBAL]);Null;IIf([ToTBAL]>0;"GREEN";IIf([ToTBAL]<0;"RED";"Null [ToTBAL]=0")))

or

IIf(IsNull([ToTBAL]);Null;IIf([ToTBAL]>0;"GREEN";IIf([ToTBAL]<0;"RED";""BLUE" [ToTBAL]=0")))

This is also not working

[hapm]

IIf(IsNull([ToTBAL]);Null;IIf([ToTBAL]>0;"GREEN";IIf([ToTBAL]<0;"RED";"BLUE")))

[aamer]

IIf(IsNull([ToTBAL]);Null;IIf([ToTBAL]>0;"GREEN";IIf([ToTBAL]<0;"RED";"BLUE")))

by using the above I get the following error

The expression you entered contains invalid syntax.

You omitted an operand or operator, you entered an invalid character or comma, or you entered text without surrounding in quotation marks.

[hapm]

My fault: replace ; by ,

[aamer]

Thx worked like a charm