Scaling of decimal value resulted in data truncation

[Jeannethe]

I created several queries to calculate unit cost and then extended cost
depending on the number of units. The queires ran fine when it was a small
sample size, as soon as I extended the sample size to the complete table I
get the error "Scaling of decimal value resulted in data truncation". The
calculating fields are set to standard and 2 decimal places. The data is
linked with an ODBC connection.

[Allen Browne]

Jeannethe, I'm not sure what has caused the particular problem you describe,
but nothing would be surprising working with Decimal fields in Access.

It can't even perform a basic sort on this data type:
http://allenbrowne.com/bug-08.html
and in A97 the data would just disappear if you tried to programmatically
assign Decimal type data to display in a form or report.

Presumably you have Precision set to some large number such as 28, and Scale
set to 2, and you are fully aware that any maths performed on the field will
result in no more than 2 decimal places.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

Post by Jeannethe
I created several queries to calculate unit cost and then extended cost
depending on the number of units. The queires ran fine when it was a small
sample size, as soon as I extended the sample size to the complete table I
get the error "Scaling of decimal value resulted in data truncation". The
calculating fields are set to standard and 2 decimal places. The data is
linked with an ODBC connection.

[Jamie Collins]

Post by Allen Browne

Post by Jeannethe
as soon as I extended the sample size to the complete table
I get the error "Scaling of decimal value resulted in data
truncation".

Jeannethe,
I don't think the data type is at fault here, rather your rounding rule
(or the one atomically applied for you). You shouldn't have any
problems if you explicitly round/curtail your values according to your
column's scale and precision. Do you have any test data with which we
can reproduce the problem?

Post by Allen Browne
nothing would be surprising working with Decimal fields in Access.
It can't even perform a basic sort on this data type

Allen, inability to sort negative values in descending order (easily
worked around using suitable type casting) does not condemn a data
type. Jet uses the DECIMAL data type internally so it is unavoidable
anyhow <g>.

Jamie.

--