If/Then Null based on Null Value of Another Field

[vbpeterson]

HELP PLEASE!!



I am having trouble creating an expression whereby the value of [ThisExpr] is conditioned upon whether (NZ[OtherExpr]) is Null. So, If (NZ[OtherExpr]) returns a null value then 0, or If [OtherExpr] is not Null then [ThisExpr].

Hope that makes sense.

Thank you, Kim

PS I am running Access 2007, not 2003 as shown above.

[orange]

I think you have some misunderstanding of the NZ() function.
For examples see
http://www.techonthenet.com/access/f...dvanced/nz.php

[vbpeterson]

"NZ[OtherExpr]" was just my way of trying to represent the "other expression field" in which I used the NZ function. The expressions are actually shown as below. Thank you

NewAssessedValue: (NZ([PropertyNewAppraisedValue],0)*0.4)

NewTaxAmount: ([NewAssessedValue]*[MilageRate])

TaxSavings: ([OriginalTaxAmount]-[NewTaxAmount])

I need the value for TaxSavings to be 0 when the value for NewAssessedValue is 0.

[orange]

My first thought is to adjust NewAssedValue as


NewAssessedValue: iif(NZ([PropertyNewAppraisedValue],0)= 0,0,[PropertyNewAppraisedValue]*0.4)

This is saying
If the [PropertyNewAppraisedValue] is NULL, give me a 0, so
if [PropertyNewAppraisedValue] =0 then NewAssessedValue will be 0

if [PropertyNewAppraisedValue] <>0 then NewAssessedValue will be [PropertyNewAppraisedValue]*0.4

[vbpeterson]

That makes sense. However, it doesn't solve my Tax Savings calucation issue. If PropertyNewAppraisedValue is 0 and NewAssessedValue is 0 I will still get a TaxSavings value based on OrigTaxAmt-NewTaxAmt. I need Tax Savings to be 0 if PropertyNewAppraisedValue has not been entered yet.

[orange]

Hmmm....
TaxSavings: ([OriginalTaxAmount]-[NewTaxAmount]) 'Your original

TaxSavings: ([OriginalTaxAmount]-([NewAssessedValue]*[MilageRate]) 'Substituting NewTaxAmount


and substituting NewAssessedValue
TaxSavings: ([OriginalTaxAmount]-(iif(NZ([PropertyNewAppraisedValue],0)= 0,0,[PropertyNewAppraisedValue]*0.4)*[MilageRate])

I think TaxSavings as a calculation holds true. I'm not sure where "if PropertyNewAppraisedValue has not been entered yet" fits.

If it hasn't been entered, does that mean it's NULL, so NZ(...,0) will be 0 and the calculation in red will be 0, so Tax Savings will be the OriginalTaxAmount - 0.

[vbpeterson]

"If it hasn't been entered, does that mean it's NULL, so NZ(...,0) will be 0 and the calculation in red will be 0, so Tax Savings will be the OriginalTaxAmount - 0. "

Exactly, and that is the problem. If the New Appraised Value has not been entered yet I need the tax savings to be 0, not OrigTaxAmt - 0.

[orange]

Part of the issue I'm seeing is that you don't have a mathematical expression. You have calculated fields, and those are dependent on someone having entered something.

So iif(NZ([PropertyNewAppraisedValue],0)= 0 then OriginalTaxAmount means nothing, so set it to 0


(A)OriginalTaxAmount =iif(NZ([PropertyNewAppraisedValue],0)= 0 ,0,OriginalTaxAmount)

TaxSavings=([OriginalTaxAmount]-[NewTaxAmount]) and the whole thing comes down to

taxsavings = originalTaxAmount - newTaxAmount which becomes

TaxSavings = iif(NZ([PropertyNewAppraisedValue],0)= 0 ,0,OriginalTaxAmount) - (iif(NZ([PropertyNewAppraisedValue],0)= 0,0,[PropertyNewAppraisedValue]*0.4)*[MilageRate])


I just strung the pieces together.

[vbpeterson]

This calulation is meant to show how much is saved (the difference) in property taxes after a property value appeal. There is an OriginalAppraisedValue field which will always have a value input. From that value the OriginalTaxAmount is calculated. So there is always an OriginalTaxAmount, not 0. The NewAppraisedValue is not input until after the tax appeal. So, as it is written now, when the fields are calculated before the NewAppraisedValue is input the TaxSavings is as you said, OriginalTaxAmount - NewTaxAmount, since the NewTaxAmount at this time is 0 the answer is the same as OriginalTaxAmount, which is incorrect because having the answer the same as the OriginalTaxAmount is not how much will be saved (the difference between the original and new tax amount). I need the Tax Savings to be 0 until a value is input in the NewAppraisedValue field.

I hope that makes sense. I appreciate your help. Please stick with me

[orange]

Did you try the set up in the post I sent?

[vbpeterson]

I have put it your setup and am testing. Must run some errands, be back later this evening. Thank you so much.

[vbpeterson]

Dear orange, So sorry for not getting back to you sooner. I haven't been able to get back to working out this problem until today. Other priorities getting in the way. Your fix worked perfectly! Of course THank you very much for your help.