424,301 Members | 1,295 Online
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,301 IT Pros & Developers. It's quick & easy.

# NBV To Zero if There is Disposal

 P: 22 I have a query with the following fields cost, AccDpr, Disposal and NBV, what i want to do is to returned NBV to zero when their is any value i.e amount in disposal field. The NBV formula = [cost]-[Accdpr]. Pls Help Jul 12 '18 #1

#### ✓ answered by twinnyfo

I see. Thank you.

Just change the Formula to:

Expand|Select|Wrap|Line Numbers
1. = IIf([Disposal] Is Not Null, 0, [cost]-[Accdpr])
Hope this hepps!

10 Replies

 Expert Mod 2.5K+ P: 2,896 Mafi, My first advice to you is that you should never have a calculated field as part of a table. The MBV value should always be calculated in the query or report that uses the other three values. This eliminates your current concerns, and the MBV value will always be current and accurate. Hope this hepps! Jul 12 '18 #2

 P: 22 All my calculated fields are in query not part of table. Jul 12 '18 #3

 Expert Mod 15k+ P: 31,186 Is this as simple as saying : Expand|Select|Wrap|Line Numbers IIf([Disposal]=0,0,[Cost]-[AccDpr]) AS [NBV] Jul 12 '18 #4

 Expert Mod 2.5K+ P: 2,896 I see. Thank you. Just change the Formula to: Expand|Select|Wrap|Line Numbers = IIf([Disposal] Is Not Null, 0, [cost]-[Accdpr]) Hope this hepps! Jul 12 '18 #5

 Expert Mod 2.5K+ P: 2,896 And NeoPa and I think alike! Jul 12 '18 #6

 P: 22 Twinnyfo u get it right but i did a little modification to work perfectly for me. = IIf([Disposal] Is Null, 0, [cost]-[Accdpr]). Thanks. Jul 13 '18 #7

 Expert Mod 2.5K+ P: 2,896 But I thought that if there was a value in Disposal, that NBV was set to 0? Your formula says the opposite, that if Disposal is empty, NBV is 0. Jul 13 '18 #8

 P: 22 Neopa thank you also for ur contribution. Jul 13 '18 #9

 Expert Mod 15k+ P: 31,186 Interesting. So, when you say "When [Disposal] has no value" you actually mean when there is no value set, rather than when the value is zero (0). That's a hard concept to express clearly so it's understandable you left us confused. Mafi: what i want to do is to returned NBV to zero when their is any value i.e amount in disposal field. If your adjusted code is what you want then your original question was back-to-front. As such, Twinny's answer was perfect and I'll set it as Best Answer. That doesn't mean you have to use it directly, but anyone else looking for a solution to a similar question will find his answer most helpful. Jul 13 '18 #10

 Expert Mod 15k+ P: 31,186 Mafi: Neopa thank you also for ur contribution. My pleasure. If I'd understood you better I'd have given you the same solution Twinny did, but they were both close enough to work from. Glad you're sorted :-) Jul 13 '18 #11