Formula Result Contains Extra Dec Places and "Invalid Data"

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Doug Carlson - 19 Aug 2004 19:45 GMT

A formula involving a decimal number will seemingly randomly return
additional decimal places which in turn causes the control to be outlined in
red dashes (Invalid Data). (The additional decimals are not repeating.)
Sometimes a number like 1234.56 is returned and other times a number like
456.78000000 is returned. The latter results in the error. The formula is
on a text box rule to set the value of another field.

Thanks for any insight or explanation.

Reply to this Message

Josh Bertsch [MSFT] - 19 Aug 2004 20:13 GMT

Does the article below seem to be similiar to what you are experiencing?

http://support.microsoft.com/default.aspx?scid=kb;en-us;822024

--josh bertsch

> A formula involving a decimal number will seemingly randomly return
> additional decimal places which in turn causes the control to be outlined in
[quoted text clipped - 4 lines]
>
> Thanks for any insight or explanation.

Reply to this Message

Doug Carlson - 20 Aug 2004 16:39 GMT

The article was helpful if only to the extent that it reassured me that I
wasn't crazy because there apparently are bugs in the way InfoPath processes
formulas.

In my situation, the formula is calculating the correct result, but it is
unnecessarily (and randomly) carrying the calculation out to additional
decimal places. For example 2.1 * 2 = 4.2. But 2.1 * 3 = 6.300000000. What
for?!

Painstaking discovered workaround:
InfoPath displays a maximum of 9 decimal places when the "Auto" option is
selected on for the format. However 15 decimal places are actually
calculated internally and apparently, randomly.

If the data connection is an Access database, the scale must be >= 15 for
the column to which the text control is bound, (the control to contain the
results of the formula).

I don't know it this applies to databases other than Access. And
fortunately, in this situation, data types and/or decimal scale properties
could be changed without creating issues with other applications using the
database.

If there's a better explanation or work-around to this, please reply.
-Thanks.

> Does the article below seem to be similiar to what you are experiencing?
>
[quoted text clipped - 12 lines]
> >
> > Thanks for any insight or explanation.

Reply to this Message

Gary Hsu [MSFT] - 21 Aug 2004 00:23 GMT

Yes, when you set number formatting on a field, InfoPath displays a maximum
of 9 decimal places due to OS restrictions. (You can choose not to format
the number for display in the property dialog in SP1.) Internally, InfoPath
uses XPath to evaluate the formula expression. XPath uses double data type
for numbers. Double is a floating point data type. Floating point data
types will experience loss of precision because of the nature of the data
type. If you search for this in MSDN, you'll find plenty of information on
this (e.g. double data type, floating limits, etc.).

The best way to solve your problem that I know is to round the result to a
fix number of decimal places. For example, "round(2.1 * 3 * 100000) /
100000" would round the decimal places to 5 digits.

Hope that helps,
Gary

> The article was helpful if only to the extent that it reassured me that I
> wasn't crazy because there apparently are bugs in the way InfoPath processes
[quoted text clipped - 38 lines]
> > >
> > > Thanks for any insight or explanation.

Reply to this Message