Using ISNULL and SWITCH functions

[DavidQMP]

Hi,

Is there anyway to include ISNULL in the below? I want to replace null values with a 0 and the last element of the below doesn't work.



(Switch([Rating]="1","0.2",[Rating]="2","0.4",[Rating]="3","0.6",[Rating]="4","0.8",[Rating]="5","1",[Rating]=" ","0")

Thanks in advance

[JoeM]

You can use the NZ function to replace Null values with zero, i.e.
NZ([Rating],0)
will return the value from Rating, but if the field is Null, it will return whatever you tell it to in the second argument (0 in our example).

[John_G]

Easier would be to use :

nz([Rating],0) / 5.0 - you don't need switch at all.

PS - the reason your original statement didn't work is that " " isn't Null - it's a string of one blank. "" isn't Null either - it's an empty string.

[CJ_London]

if you just want a default when rating isn't 1-5, then just use True (no quotes) instead of 'rating=" "''

[DavidQMP]

You can use the NZ function to replace Null values with zero, i.e.
NZ([Rating],0)
will return the value from Rating, but if the field is Null, it will return whatever you tell it to in the second argument (0 in our example).

Thank you very much, I nested the switch functions within a Nz function.