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

NBV To Zero if There is Disposal

P: 14
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
1 Week Ago #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 100+
P: 2,443
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!
1 Week Ago #2

P: 14
All my calculated fields are in query not part of table.
1 Week Ago #3

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

twinnyfo
Expert Mod 100+
P: 2,443
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!
1 Week Ago #5

twinnyfo
Expert Mod 100+
P: 2,443
And NeoPa and I think alike!
1 Week Ago #6

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

twinnyfo
Expert Mod 100+
P: 2,443
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.
1 Week Ago #8

P: 14
Neopa thank you also for ur contribution.
1 Week Ago #9

NeoPa
Expert Mod 15k+
P: 30,869
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.
1 Week Ago #10

NeoPa
Expert Mod 15k+
P: 30,869
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 :-)
1 Week Ago #11

Post your reply

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