Working Around the NaN Issue

I was recently building an internal InfoPath 2007 solution and came across an old nemesis: the dreaded “NaN” issue. In this particular case, the NaN value appeared in a calculated field that was bound to an expression box. The calculation was a division formula that, when the form was first opened, would have a denominator equal to 0, thus yielding the NaN. Because of the way calculations were set up throughout the form, I could not avoid the 0 denominator until the user entered values elsewhere. While the NaN would eventually be replaced by a double type before the form was submitted, it was considered to be an eyesore.

If I could not beat the NaN, I figured I could work around it. I decided to add conditional logic to the expression box to hide the control when the value was not a number. Although InfoPath 2007 does not have an is not a number operator for conditional formatting, you can still use pattern matching to determine if a value is indeed a number. Following are the steps I used to hide the NaN:

  1. Double-click the expression box.
  2. In the Expression Box Properties dialog box, click the Display tab.
  3. Click Conditional Formatting.
  4. In the Conditional Formatting dialog box, click Add.
  5. In the Conditional Format dialog box, change the operator from is equal to to does not match pattern.
  6. Click the value list and then click Select a pattern.
  7. In the Data Entry Pattern dialog box, in the Standard patterns list box, click Custom pattern.
  8. In the Custom pattern box, type d*.?d*.
    Creating a Custom Pattern
  9. Click OK.
  10. In the Conditional Format dialog box, select the Hide this control check box.
    Hiding the Expression Box
  11. Click OK until all dialog boxes have been closed.

I have seen other posts on this subject, with solutions that are more elegant than mine. But this approach at least got the NaN out of my form view.

Leave a Reply

Your email address will not be published. Required fields are marked *