The decimal field's precision is too small to accept the numeric you attempted to add (1 Viewer)

[Hello1]

Its giving me this error when I run my query. I found out which field is the troublemaker by removing one by one and adding back.
The query writes data to a table.

PriceDiffofSth: Sum(([Price]/[CoefficientOK])*(1-[CoefficientOK]))
Total : Expression
Append to: PriceDiffofSth

The actual values:
PriceDiffofSth: Sum(([257.22]/[0.7])*(1-[0.7]))

PriceDiffofSth: Sum(367.4571428571429 * 0.3)

Final result should be:
PriceDiffofSth: Sum(110.2371428571429)

The table field size this query is writing to is a decimal one
Precision: 10
Scale: 2

I tried to increase the precision and scale but same, also I tried long integer and double without any luck.

 

[JHB]

..I tried to increase the precision and scale ..

How much did you increase it?
Precision = is for all digits in a number.
Scale = max number of digits to the right of the decimal separator.

 

[Hello1]

I tried quite some combos.
Precision 25, and scale 22
Precision 25, and scale 20
Precision 18, and scale 2, and some more.

I dont know what else to try

Edit: I made a new query to do some testing.

Added another field and gave it a criteria to give me back just one record. (not so important)

PriceDiffofSth: Sum([Price]/[CoefficientOK])

I removed the rest of the expression and still get the error.
I added these 2 fields Price and CoefficientOK just to see hich values do they have when I put the query in datasheet view and yes they do have the ones I wrote before, Price = 257.22 and CoefficientOK = 0.7.

PriceDiffofSth: Sum([Price]/0.7)

Now when I exchange the CoefficientOK for the actual value in the expression it doesnt give me any errors and I can see the result in datasheet view.

PriceDiffofSth: Sum(257.22/[CoefficientOK])
Works like this also

or also like this PriceDiffofSth: ([Price]/[CoefficientOK])
Without the Sum (not the same result, but no errors)

 

[isladogs]

I don't understand where this is going wrong for you.
Both decimal and double should work fine
Single will truncate your answer & Integer/Long will just give whole numbers

Try typing the following in the VBE Immediate window as I have

?CDec(367.4571428571429 * 0.3)
 110.237142857143 

?CDbl(367.4571428571429 * 0.3)
 110.237142857143 

?CSng(367.4571428571429 * 0.3)
 110.2371 

?CInt(367.4571428571429 * 0.3)
 110 

?CLng(367.4571428571429 * 0.3)
 110

Whilst I'm at it ....

 

[Hello1]

I edited my last post just before your answer, please check it.
I did, gives me exactly the same as for you.

In the actual tables where the Price is coming from the filed is a Decimal number with precision 10 and scale 2, while the CoefficientOK field which is in another table is a Decimal number also but with precision 10 and scale 4.
Could this be the problem?

 

[isladogs]

So you did - I hadn't seen the changes

I've just created a very simple database with 2 tables, 4 records & 4 queries
Table1 : Price & Coefficient both Double data type
Query1 applies your expression to each record
Query 1A does the overall sum on your expression

Table2 is a copy of Table1 but I then changed both field to Decimal using the same values for precision/scale as you. As I did so, it warned some data may be lost (which should tell you all you need to know)
Query 2 & 2A are the same as above but based on Table2

Compare the results of each type of query.
From that, you should be able to work out what to do for your purposes

 

[Hello1]

All work just the Query2A gives me the same error, unless I remove the sum.
So the problem is the Decimal type of field, I changed the 2 fields to double and it works now.
Thanks

 

[isladogs]

That's the correct answer!