Eliminate NaN and Infinity Errors in Calculations
Page 1 of 2
Written by
Gregory Scot Collins
Tuesday, 31 March 2009, 11:47 AM
This article has been tested to work with the following products and versions. No guarantee of compatibility, with or without modification, is offered for products or versions other than those listed.
IN THIS ARTICLE:
When using calculated fields or expression boxes that incorporate division, the likelihood of receiving calculation errors is great. Not only can this look bad in the form, it can cause downstream issues for data stored in a database.
You will encounter two kinds of calculation errors. The first is the NaN error which occurs when attempting calculations with non-numerical values, including blanks. The second is the Infinity and -Infinity errors which occur when dividing by zero or a blank.
NOTE: These errors are actually useful in that they identify data problems that need correction. However, if you don't require a correction and don't want the ugly error messages, you can use the following techniques to replace these errors with a zero or a blank.
Create a test form
To start, create a test form using the following steps:
- Create a new blank form.
- Open the Data Source task pane.
- Add a Decimal field named Dividend.
- Add a Decimal field named Divisor.
- Add a Decimal field named Quotient.
- Double-click Quotient.
- In the Field Or Group Properties dialog box, click the Insert Formula button to the right of the Value text box.
- In the Formula text box type: ../my:Dividend div ../my:Divisor, and then click OK twice.
- Right-click the myFields and choose Controls In Layout Table.
Preview the form and you will note that Quotient has the value NaN, which stands for Not A Number. If you were to type 5 as the Dividend, the Quotient value changes to Infinity. Change the Dividend to -5 and the Quotient is now -Infinity. Exit the preview.
Eliminate just the NaN error
Use the following steps to eliminate the NaN error:
- In the Data Source task pane, double-click Quotient.
- In the Field Or Group Properties dialog box, click the Insert Formula button to the right of the Value text box.
- Replace the existing formula with one of the following:
- If you want a zero value, type: translate(../my:Dividend div ../my:Divisor, "aN", "0").
- If you want a blank value, type: translate(../my:Dividend div ../my:Divisor, "aN", "").
- Click OK twice.
Preview the form and you will note that the Quotient no longer has the value NaN, but zero or blank, depending on which formula you used. Exit the preview.
The translate() function replaces the character 'a' with a zero or nothing, and replaces both instances of the character 'N' with nothing, thus converting the error "NaN" into a zero or a blank.
Stumble It!
Digg It!
del.icio.us
