Calculation fields

[Mike]

I've designed a form for my offices cash collection reconcilition. The form
is based on an Access database and is supposed to calculate Cash(267 x
20's=X, 87 x50's=...), etc. Then sum(cash) + sum(checks) + sum(vouchers). It
works fine except that when certain values are entered the form returns a red
dotted box around the calculated value even though the value calculated is
displayed correctly. It seems as though it does not "like" certain numbers! I
have made sure that the fields are set to currency and have tried decimals
set to auto as well as 2. No special scripting has been used. Please help.
Thanks.

Michael

 

[Andrew Watt [MVP - InfoPath]]

I've designed a form for my offices cash collection reconcilition. The form
is based on an Access database and is supposed to calculate Cash(267 x
20's=X, 87 x50's=...), etc. Then sum(cash) + sum(checks) + sum(vouchers). It
works fine except that when certain values are entered the form returns a red
dotted box around the calculated value even though the value calculated is
displayed correctly. It seems as though it does not "like" certain numbers! I
have made sure that the fields are set to currency and have tried decimals
set to auto as well as 2. No special scripting has been used. Please help.
Thanks.

Michael

Mike,

The dotted red lines indicate that a value is not valid.

There are two possible causes that come to mind:

1. The result of a calculation isn't valid as a value to be sent to
the corresponding field in Access.

2. You have inadvertently set the data type of the field in InfoPath
to a type that is incompatible with the value.

If a small number of fields are affected I would look carefully at
their datatype and compare it with the design of the corresponding
field in Access.

I assume you don't inadvertently manually enter a $ or other currency
symbol in a decimal field.

Andrew Watt
MVP - InfoPath

 

[Bob P]

I ran into something very similar. I has a formula (List price) *
(Discount) = (Net Price). It worked fine except for certain values;
for example a discount of 57% would cause the Net Price to have the
red border.

The problem was the SQL data type the fields used. With a 'money'
data type, the calculation may have too many decimals. Either change
the data type or use a formula like
(round(Qty * Discount * 100)) / 100

Bob

 

[Mike]

Thanks Bob. It worked perfectly. I don't know why the program won't accept
certain values(even whole numbers) but the "round" function does remedy it.