#Error problem

[casey]

Hello all,

I have several calculated textboxes on a form which take values from other textboxes in subforms. Many of them work fine, but a few display #Error when the textboxes they reference have no value. (Mainly when the form's navigated to a new record.) It really looks bad when an application has #Errors showing up all over the thing. I would prefer that they just be blank if such is the case. Any ideas how to alleviate this problem?

 

[Travis]

Try using the Null to Zero (Nz) function in your calculation:

=Nz([TextField1],0)+Nz([TextField2],0)

 

[casey]

Thanks for your reply, Travis.

I thought that would work, but no dice. It's strange that most of the other calculated controls on the form have no problem, while a few do this and they're all set up the same way!

 

[boblarson]

Make sure that the text box names for all of your forms, do not have the same name as your table field names. When you use the wizard to create a form, Microsoft, in their infinite wisdom, made it so that the control names are the same as the table field names which causes all sorts of #Error problems especially with calculated values.

 

[casey]

Wow!

To change every textbox name on my forms would be a major pain.

And to think that all this time, I've been using the same names as my tables figuring that the references would be through the forms. Like you say, especially considering that MS names fields when their dropped onto the form!

I'll give your suggestion a try when I get a five minutes.

Thanks for your help.

 

[casey]

I finally figured this out

And the winner....

This #Error would show up only on a few calculated controls(mainly when the user moved to an autonumber field). To alleviate the problem, I merely added an IIF() statement in the calculated control that tested the autonumber field for a null condition. It worked!!! Thanks.