Getting the #Num! Error

[TZyk2003]

Good afternoon everyone!

I have this as a control source in my access report.

=([Test_Cond_Cycle_0_Comp]+[Test_Cond_Cycle_1_Comp]+[Test_Cond_Cycle_2_Comp])/([Test_Cond_Cycle_0_Plan]+[Test_Cond_Cycle_1_Plan]+[Test_Cond_Cycle_2_Plan])

It works great EXCEPT when it ends up with 0 / 0 which access prints out as "#Num!"

Anyone know how I can make access change #Num! to 0???

Thanks!!

-Tim

 

[LarryDeLaruelle]

Tim:

Check help for the Nz function.

If any of your fields contain a Null value the result of the calculation will be null; the Nz function will convert it (sort of) to a zero value to allow proper calculations.

Not positive that this is the cause of your problem but it is worth a look.

Of course, if you are actually trying to divide by zero and not just getting an incorrect result because of nulls you will get the error message since division by zero is invalid.

If it is possible that the actual calculation will result in an attempt to divide by zero you will need to set up some edit checking to test for that condition. Not sure, but you may be able to use an Immediate If (IIf) in the equation for the test. Another option would be to create a custom function; more work but reusable.

HTH.

Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[scriverb]

This error as you described is most likely caused by a divide by zero(0) which it can't do. So, here is an updated expression that will provide for a zero(0) answer if the conditions warrant it:

= IIF((([Test_Cond_Cycle_0_Plan]+[Test_Cond_Cycle_1_Plan]+[Test_Cond_Cycle_2_Plan])) = 0, 0, ([Test_Cond_Cycle_0_Comp]+[Test_Cond_Cycle_1_Comp]+[Test_Cond_Cycle_2_Comp])/([Test_Cond_Cycle_0_Plan]+[Test_Cond_Cycle_1_Plan]+[Test_Cond_Cycle_2_Plan]))

This IIF function analyzes the dividend portion of your expression and if it equals zero(0) then just returns 0 without the expression being executed. If not equal zero(0) then proceeds with your original expression evaluation.

Good luck. If I missed a paren or something just get back with me and I will look it over again. Can't test it at this time.


Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[TZyk2003]

That worked great Bob! Thanks Bob and Larry!!

-Tim

 

[scriverb]

It is a please to help you. Good luck with your project.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[Dukester0122]

Scriverb,

The following formula is also giving me the division by zero message. How do I use the IIF statement to correct this?

{@InvPrice}/{@Invoice}

Thanks,

Dukester

 

[scriverb]

Now I used your posting exactly. I see that you have squilly brackets around both the dividend and divisor. Now sure about them but I included them in this answer:

IIF({@Invoice} = 0, 0, {@InvPrice}/{@Invoice})

This should work for you.


Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???