avoid showing errors

[dmyoungsal]

on my form, I have a number of textboxes that perform calculations. As long as there is data in the corresponding cells, everything works well.

The issue I am having (this is more cosmetic than anything), is to not show the errors in these boxes before data is entered. I have one showing "#Num!" and the others are "#Type!".

What can I do to not show these? I have tried IfError and IsError statements but those show other errors.

Suggestions?

 

[CJ_London]

use an iif statement to cater for the error - I presume you know what is causing the problem. e.g.

=iif(somecontrol=0,null,acontrol/somecontrol)

to avoid divide by zero errors. null will show a blank. substitute a 0 if required - do not substitute text such as "N/A", instead use control formatting

;;;"Not yet calculated"

will display Not yet calculated if the value is null

 

[sneuberg]

If you would put these expressions in functions you could use the debugger to track down this sort of problem.

 

[dmyoungsal]

CJ London - Thank you, I was heading down the IIf path, but was trying everything but "0".

Where is the "Thanks Button" (as mentioned in your footer).

 

[CJ_London]

to the right of the post - see a thumb up icon

 

[dmyoungsal]

nope, I see no "thumb"

 

[dmyoungsal]

I just saw it... thanks again!

 

[yupstrips01]

Re: avoid error and displays in formulas

Sometimes a formula may return an error message. Usually, you'll want to know when a formula error occurs. But now and then you may prefer to avoid the messages. You can do so by using an IF() function to check for an error.

For example, the formula below displays a blank if the division results in an error.

=IF(ISERROR(A1/B1),"",A1/B1)
You can adapt this technique to any operation. The original formula serves as the argument for the ISERROR() function, and it repeats as the last argument of the IF() function. Like this:

=IF(ISERROR(OriginalFormula),"",OriginalFormula)

 

[CJ_London]

I presume you are talking about Excel?. In access, for numerical calculations, although you can return text as an alternative value, it is not a good idea as it can cause problems (e.g. with formatting or subsequent calculations).

in Excel I prefer to use

=IFERROR(A1/B1,0)

Note that ISERROR in access is used to trap error codes, not the fact there is an error. e.g.

=ISERROR(1/0) in Access will return a message box saying 'Division by zero'

whereas
=ISERROR(1/0) in Excel will return TRUE