Sum Formula

[chrisguk]

Hi,

Anyone know whats wrong with this. Its keeps returning missing parenthesis or Vertical Bar?

=llf(Nz(Nz([AmountDifference]+[12MonthCostAvoidance]+[CashReturn]),0)=0,Null,(Nz(Nz([AmountDifference]+[12MonthCostAvoidance]+[CashReturn]))

 

[spikepl]

print it out on a piece of paper, and connect the correponding parantheses with a pen

 

[neileg]

Dunno what you're trying to do. You seem to have double Nz functions in two places both of which are missing a closing parenthesis. The additions inside the parentheses will return a null if any of the values are null, so I don't think your formula is going to give you what you want. You may have to use Nz on each individual value.

Also you could use IsNotNull instead of Nz and the =0 comparator.

 

[chrisguk]

Dunno what you're trying to do. You seem to have double Nz functions in two places both of which are missing a closing parenthesis. The additions inside the parentheses will return a null if any of the values are null, so I don't think your formula is going to give you what you want. You may have to use Nz on each individual value.

Also you could use IsNotNull instead of Nz and the =0 comparator.

Hi Ok this is what I am trying to achieve. I want to be able to check for a null value in the following formulas:

=Nz([OriginalAmount])/Nz([dteOriginalTo]-[dteOriginalFrom])*365

and

=(Nz([OriginalAmount]-[RebillAmount]))+Nz([OriginalAmount])/Nz([dteOriginalTo]-[dteOriginalFrom])*365+Nz([CashReturn])

Can anyone help with this?

 

[neileg]

Still don't understand. You're using Nz so any nulls will be converted to zero. Do you want to check if any field is null or indentify which field is null and what do you want to happen if there is a null?