434,985 Members | 2,877 Online
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,985 IT Pros & Developers. It's quick & easy.

# Addition expression that handles null values

 P: 5 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: Expand|Select|Wrap|Line Numbers 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. Expand|Select|Wrap|Line Numbers 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 Nov 12 '13 #1

#### ✓ answered by CAintheUK

not tested but have you tried

Expand|Select|Wrap|Line Numbers
1. 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))

5 Replies

 P: 16 not tested but have you tried Expand|Select|Wrap|Line Numbers 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)) Nov 12 '13 #2

 P: 5 Thank you, "CA in the UK". That worked well. I appreciate the help. Best regards, Gerry Nov 12 '13 #3

 P: 16 as a newcomer, I'm pleased to help Nov 13 '13 #4

 Expert Mod 15k+ P: 31,492 An alternative : Expand|Select|Wrap|Line Numbers 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. Nov 14 '13 #5

 P: 5 Hey, Thanks NeoPa! Nov 14 '13 #6