Sum Function Error

[RichW]

I have a form containing a textbox with a control source of:

=Sum(([Item_12_Reactive]/([Item_12_Reactive]+[Item_12_Anticipatory]+[Item_12_Participative]+[Item_12_Consultative])*100))

When records contain the data that is used by this function, the text box
correctly displays the sum. But, when any single piece of data is not
contained in a record, the sum is not displayed, but is replaced with the
word "Error".

Instead of the word "Error", I would rather have the textbox display "0".

Sure would appreciate this groups advice.

Thank you,

 

[Larry Daugherty]

You might create a real function and test the value of the denominator
components in it. If a combination would result in division by 0 then
skip the calculation, set the value of the function to 0 and return.

Of course, with a real function you can insert an Error Handler that
will return a message box with a meaningful error message if you
choose.

HTH

 

[RichW]

Larry,

Thanks for the help.

How would I skip the calculation if the result was 0?

Thanks again,

 

[GB]

This is an interesting issue indeed. You indicated that if any one of the
items was not present in the data that you get #Error showing; however, it
appears that without using some sort of VBA, that you can't test and display
in this one field to see if you should present a 0, because all of the test
operations (Choose, IIF, Switch) evaluate all possible responses and will
error if one of the answers is an error. :/


Thinking a little out of the box, I would recommend a non visible text box
that contains your result from the function you have built, then use a
visible text box that uses the first text box as it's control source. Such
that you test if the other text box is equal to an error, if it is an error,
then present 0, otherwise present the value of the "hidden" text box.

So something like:

=IIf(iserror(textbox),0, textBox) (Or may have to test the value stored in
the textbox and compare to text. I don't know/haven't tested this code, but
it's the right idea I think.)

 

[RichW]

GB,

Thank you for the advice. It works!

Unless there exists a more effective way of handling this error, than I'm
going with your advice.

Thanks again & best regards,

 

[GB]

I did think about Larry's assistance a little more too. I also am not
entirely sure how to initiate it, but what he was describing was to make a
module that is available from the "design" window (You know where you can
choose Tables, Queries, Forms, Reports, etc..) that could be called and
return a value (I.e., a function.) I'm thinking that if you create a module,
then within it create a function that does the test you are looking for, you
can do a full check to see if the value is an error or not, and if it is an
error, then provide 0 otherwise provide the result.

Public Function Answer() as long
if iserror([your equation using appropriate references]) then
Answer = 0
else
Answer = [your equation using appropriate references]
end if

end function

And then you only need one field to be displayed (or present, i.e. no
"hidden" field) and set the control source to the module.

Using VBA like this, you don't run into the problem that I described with
the other three functions that are available in the control source.