Problem with decimal fields and multiplication

[kfrost]

Hello,

I have a form that connects to a SQL database. I've got three fields in a
repeating section called Gallons, PPG, Cost.

All three fields in SQL are decimal with 6 for scale and 2 for precision.
(Meaning all numbers should be 2 decimal places. For the cost field it is a
text box with the same settings. The formula set for it is @Gallons * @PPG

However, sometimes if you enter in say 157.89 for gallons and 2.11 for PPG
these fields will show up with a Red field around them saying invalid
entries. Some times you can take and delete the numbers and retype them and
everything is ok.

I have a similar problem with the cost field. Some times the cost field
will have value of something like 333.14790000 even though you specify in the
properties of the field to limit the number to 2 decimal places.

I would like to set the cost field as read only but with these problems I
can't. Any ideas as to what might be going on?

Thanks.

kris

 

[kfrost]

FYI. I'm also having this problem with fields I'm using the Sum() function
with as well. I did find another thread mentioning a similiar problem which
mentions to use the round function. Going to give it a try. If anybody has
any more suggestions, please let me know.

 

[kfrost]

another thing to add. I get the invalid data error even when everything
looks ok. For example I have a Total Cost that uses the Sum(@cost) to get
it's value. Right now if you open the form, it's highlighted as invalid data
when loaded from SQL. It's the same value it has previous input. The value
right now is 1,146.62. If you take and delete the figure and retype it in,
the invalid data error goes away.

Puzzling

 

[kfrost]

Hello Alex,

The data coming in is only 2 decimal places not 3 as you indicate below.
Actually there are only two entries in the database and they were created via
a separate view in infopath. I go and check in the SQL database and the
value is only 2 decimal as it should be.

Another curious thing is I found that just changing the numbers around in
ppg seemed to change the results. For example, for one entry I had 157 in
the Gallons field and entered 2.13 for ppg and the result was a value with 6
decimals. In testing anything below 2.11 and above the value of 2.15, the
calculation worked fine. I think there is some type of bug here.

At any rate, I am using the round function for these fields. However, how
would you go about using it in conjunction with the Sum() function?

I have a couple of other fields where use the sum() option to sum all the
values of another field. I get the same type of quirkiness. Any examples of
how to do this?

Thanks.

kris

The reason why typing the number in manually fixes the problem is as
following:

- Data coming from the SQL server is something like "1446.623". InfoPath
data formatting happens only on the presentation level - so the XML
stores the higher-precision number, while what you see in the text box
is a lower precision number (2 decimal digits).
- Data validation happens on the Data (against the XML). What's in the
XML doesn't satisfy the criteria of "2 digits after decimal point".
- When you remove the value from the text box, and type it in again
manually, you modify what's in the XML - so there is no data validation
error any more.

Like you mentioned yourself, I would recommend using the round()
function to solve these problems. Let me know if you need help with it.

Good luck,
Alex @ Microsoft

-----Original Message-----
From: kfrost [mailto:[email protected]]
Posted At: Sunday, May 15, 2005 9:50 AM
Posted To: microsoft.public.infopath
Conversation: Problem with decimal fields and multiplication
Subject: RE: Problem with decimal fields and multiplication

another thing to add. I get the invalid data error even when everything

looks ok. For example I have a Total Cost that uses the Sum(@cost) to
get
it's value. Right now if you open the form, it's highlighted as invalid
data
when loaded from SQL. It's the same value it has previous input. The
value
right now is 1,146.62. If you take and delete the figure and retype it
in,
the invalid data error goes away.

Puzzling

Hello,

I have a form that connects to a SQL database. I've got three fields in a
repeating section called Gallons, PPG, Cost.

All three fields in SQL are decimal with 6 for scale and 2 for precision.
(Meaning all numbers should be 2 decimal places. For the cost field it is a
text box with the same settings. The formula set for it is @Gallons * @PPG

However, sometimes if you enter in say 157.89 for gallons and 2.11 for PPG
these fields will show up with a Red field around them saying invalid
entries. Some times you can take and delete the numbers and retype them and
everything is ok.

I have a similar problem with the cost field. Some times the cost field
will have value of something like 333.14790000 even though you specify in the
properties of the field to limit the number to 2 decimal places.

I would like to set the cost field as read only but with these problems I
can't. Any ideas as to what might be going on?

Thanks.

kris