By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,985 Members | 2,877 Online
Bytes IT Community
+ 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
  1. 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
  1. 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))

Share this Question
Share on Google+
5 Replies


P: 16
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))
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

NeoPa
Expert Mod 15k+
P: 31,492
An alternative :
Expand|Select|Wrap|Line Numbers
  1. 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

Post your reply

Sign in to post your reply or Sign up for a free account.