Percentage Calculation Problem

[access2010]

I have not been able to solve our calculation problem “IF” the Net Share Cost has a value of $0.00.

(If the Net Share Cost is BLANK or $0.00, it means that this stock has / was not purchased.

Your suggestion will be appreciated.

Problem – Field = Percentage_PC
=([Current_Price]-[Net_Share_Cost])/[Net_Share_Cost]

Thank you, Nicole

 

[cheekybuddha]

It's not really clear what your problem is here, Nicole, but at a guess you might try:

=IIf(Not IsNull([Net_Share_Cost]) AND [Net_Share_Cost] > 0, ([Current_Price]-[Net_Share_Cost])/[Net_Share_Cost], 0)

 

[access2010]

It's not really clear what your problem is here, Nicole, but at a guess you might try:

=IIf(Not IsNull([Net_Share_Cost]) AND [Net_Share_Cost] > 0, ([Current_Price]-[Net_Share_Cost])/[Net_Share_Cost], 0)

Thank you CheekyBuddha for your suggestion.
I am sorry that I did not explain our problem properly.

When the value in “Net_Share_Cost” is $0.00.
Meaning that this stock was not purchased.
Then an error occurs in the field “Percentage_PC”.

If the “Net_Share_Cost” field is empty or contains a value other than 0, no error occurs.
Niciole

 

[The_Doc_Man]

The problem is that IIF doesn't short-circuit its computation. More precisely, VBA always computes BOTH SIDES (the true AND false arguments) of an IIF. This is what I meant when I say "no computational short circuit". Therefore, you are getting an unexpected divide-by-zero or some other math error - I.e. a zero/null Net_Share_Cost - even though you want to ignore the formula when you have that condition.

The simplest solution is to build a public function that takes in Current_Price and Net_Share_Cost as arguments, does a formal IF-THEN-ELSE-ENDIF block with the IF condition being the same as you used for your IIF test, and either return the result of your formula or a 0. Because you use the IF-THEN-ELSE-ENDIF block, VBA will only execute the THEN or the ELSE but won't execute the other part. Unlike IIF, which does NOT short-circuit alternatives, the IF block will only execute the "live" side of the block.

There are other options involving the Form_Current event, but if either of those fields is editable, then you would also need to have other VBA code to recompute the values.

 

[June7]

Any math with Null should return Null. If field has 0 then division by 0 will error. A custom function is one way to go. Otherwise, try:

=([Current_Price]-[Net_Share_Cost])/IIf([Net_Share_Cost] = 0, 1, [Net_Share_Cost])