If Statement in textbox if the results show as #Error

[Jay]

I have a textbox where its doing % change from two other textboxes. And
sometimes the other two other textboxes have 0 as values and it won't be able
to do the calculation. I am getting two errors...#Error and #Num!. How can
i fix that so it shows blank instead of the #Error and #Num!.....

Thank you,

Jay

 

[mscertified]

You need to change your formula to cater for the possibility of zero (or
null) in the other textboxes. Without knowing your formula, I can't specify
what to do.

-Dorian

 

[Klatuu]

wrap the text box references in the Nz function, for starts.

=Nz(Me.SomeTextBox,0)
It will change any Nulls to a 0.
If you text box has nothing in it, it is not 0, it is Null.
Now if it is addition, subtraction, or multiplication, that should take care
of it. If there is any division, then you can't divide by 0. You will have
to analyze what you want to happen if a divisor is 0. Sometimes it may be
enought to use 1 instead of 0 in the Nz function. That way, it will divide
by 1 instead of 0.

 

[Jay]

mscertified,
Here is the formula that i used.........its a simple % formula.
=[Text46]/[Text29]-1
Sometimes Text46 or Text29 is blank and sometimes both of them are blank.
I hope someone can help.

 

[Pieter Wijnen]

A bit dependant on how blanks should be treated.
=IIF(Nz(Text29,0)=0,0,Nz(Text46,0)/Text29) -1

is one way to treat it

Pieter

mscertified,
Here is the formula that i used.........its a simple % formula.
=[Text46]/[Text29]-1
Sometimes Text46 or Text29 is blank and sometimes both of them are blank.
I hope someone can help.

You need to change your formula to cater for the possibility of zero (or
null) in the other textboxes. Without knowing your formula, I can't
specify
what to do.

-Dorian