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

NBV To Zero if There is Disposal

P: 20
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!

Share this Question
Share on Google+
10 Replies


twinnyfo
Expert Mod 2.5K+
P: 2,532
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: 20
All my calculated fields are in query not part of table.
Jul 12 '18 #3

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

twinnyfo
Expert Mod 2.5K+
P: 2,532
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!
Jul 12 '18 #5

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

P: 20
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

twinnyfo
Expert Mod 2.5K+
P: 2,532
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: 20
Neopa thank you also for ur contribution.
Jul 13 '18 #9

NeoPa
Expert Mod 15k+
P: 30,996
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

NeoPa
Expert Mod 15k+
P: 30,996
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

Post your reply

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