If Statement in textbox if the results show as #Error

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jay - 02 Nov 2007 21:50 GMT

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

Reply to this Message

mscertified - 02 Nov 2007 22:42 GMT

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

> 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
[quoted text clipped - 4 lines]
>
> Jay

Reply to this Message

Jay - 06 Nov 2007 15:57 GMT

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
[quoted text clipped - 10 lines]
> >
> > Jay

Reply to this Message

Pieter Wijnen - 07 Nov 2007 08:48 GMT

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.
[quoted text clipped - 20 lines]
>> >
>> > Jay

Reply to this Message

Klatuu - 02 Nov 2007 23:05 GMT

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.

Signature

Dave Hargis, Microsoft Access MVP

> 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
[quoted text clipped - 4 lines]
>
> Jay

Reply to this Message