Addition expression that handles null values

[gaf44]

Hi,

I am trying to write an expression that will add two fields that sometimes can contain Null values. Retaining the 'null' aspect is important because I need to know when no data at all was recorded for a particular row.

At first I tried this code:

LY34_SUB1: Nz([LY3_TREE_SUB1],0)+Nz([LY4_TREE_SUB1],0)

However, if both fields are Null then it spits back 0. I want it to stay null.

So I tried adding an iif statement to my expression where if the above code was equal to 0 then the TRUE condition of the Iif statement would be a NULL and FALSE would be the sum of the two values with 0's substituted in using the Nz function.

LY34_SUB1: Iif(Nz([LY3_TREE_SUB1],0)+Nz([LY4_TREE_SUB1],0)=0),Null,Nz([LY3_TREE_SUB1],0)+Nz([LY4_TREE_SUB1],0)]

This still doesn't work. Suggestions?

Thanks,

Gerry

[CJ_London]

not tested but have you tried

LY34_SUB1: Iif(isnull([LY3_TREE_SUB1]) and isnull([LY4_TREE_SUB1]),[LY3_TREE_SUB1],Nz([LY3_TREE_SUB1],0)+Nz([LY4_TREE_SUB1],0))

[gaf44]

Thank you, "CA in the UK". That worked well. I appreciate the help. Best regards, Gerry

[CJ_London]

as a newcomer, I'm pleased to help

[NeoPa]

An alternative :

LY34_SUB1: IIf([LY3_TREE_SUB1]+[LY4_TREE_SUB1] Is Null,Null,Nz([LY3_TREE_SUB1],0)+Nz([LY4_TREE_SUB1],0))

NB. This doesn't mean there is anything wrong with CA's code or approach. It simply illustrates an alternative way of looking at it.

[gaf44]

Hey, Thanks NeoPa!