Decimal fields's precision is too small to accept the numeric youattempted to add

[Phil]

Using Jet engine version from WINXP SP2

I have a simple query which:

Works as a select query. Fails as a make table, fails as an append, and
fails if referenced by another query.

A little help would be appreciatied...

 

[Allen Browne]

The error message in the subject indicates that JET is understanding the
source field as a Decimal type, with Scale and Precision.

JET has all sorts of trouble deailing with this data type.
Could you explicitly typecast the field into a Double or Currency?
For example, if the field is Field1, you could try:
SELECT CCur(Nz([Field1],0)) AS MyField, Field2, ...

 

[Phil]

Been there, done that. Tried to use double , didn't work. Did not use
the NZ, but everything has a zero or greater value. I can try it with
it, if you think it will make a difference....

 

[Allen Browne]

Switch your Make Table query to SQL View (View menu in query design), copy
the SQL statement, and post it here.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Been there, done that. Tried to use double , didn't work. Did not use
the NZ, but everything has a zero or greater value. I can try it with it,
if you think it will make a difference....

The error message in the subject indicates that JET is understanding the
source field as a Decimal type, with Scale and Precision.

JET has all sorts of trouble deailing with this data type.
Could you explicitly typecast the field into a Double or Currency?
For example, if the field is Field1, you could try:
SELECT CCur(Nz([Field1],0)) AS MyField, Field2, ...

 

[Phil]

This fails even as a SELECT query.

SELECT customer_type.name, invoice_h.prebook_id, item_types.name,


Sum([invoice_d]![price]*((100+[invoice_d]![discount])/100)*[invoice_d]![ship_qty])
AS Extended_Gross,


Sum([invoice_d]![price]*[invoice_d]![ship_qty]) AS
Extended_Net
FROM ((((invoice_d LEFT JOIN invoice_h ON invoice_d.invoice_id =
invoice_h.invoice_id) LEFT JOIN item ON invoice_d.item_id = item.item_id)
LEFT JOIN item_types ON item.item_type = item_types.type_id) LEFT JOIN
customer ON invoice_h.customer_id = customer.customer_id) LEFT JOIN
customer_type ON customer.customer_type_id = customer_type.customer_type_id
GROUP BY customer_type.name, invoice_h.prebook_id, item_types.name;

The isolated line is the problem child. Remove it, query fine.

I changed it to
CDbl(NZ(Sum(CDbl(NZ([invoice_d]![price],0))*((100+CDbl(NZ([invoice_d]![discount],0))/100))*CDbl(NZ([invoice_d]![ship_qty],0)))))

isolating every single piece and forcing it to a Double, as well as
forcing the whole thing as a double, same answer.

 

[Allen Browne]

Okay, Phil. Now we have something specific to work with.

Let's try with just this one field, and no grouping, so we can be certain
where the problem lies:

SELECT ([invoice_d]![price] *
(1 + CDbl(Nz([invoice_d]![discount],0))/100) *
[invoice_d]![ship_qty]) AS Extended_Gross
FROM ((((invoice_d LEFT JOIN invoice_h ON invoice_d.invoice_id =
invoice_h.invoice_id) LEFT JOIN item ON invoice_d.item_id = item.item_id)
LEFT JOIN item_types ON item.item_type = item_types.type_id) LEFT JOIN
customer ON invoice_h.customer_id = customer.customer_id) LEFT JOIN
customer_type ON customer.customer_type_id = customer_type.customer_type_id

I have guessed that the discount is the one that is causing the issue, so
have forced it to double at the lowest level, and then handled it as
fractional values.

If that still fails, what are invoice_d and invoice_h? Are they queries?
Tables? Access tables? Linked dBase tables? ...?

If they are queries, you may need to perform the typecasting in the lower
queries. If tables, when you open in design view, what is the data type of
the price, discount, and ship_qty fields?

Another option is to try to break the field down further, i.e.:

SELECT [invoice_d]![price],
(1 + CDbl(Nz([invoice_d]![discount],0))/100) AS Rate,
[invoice_d]![ship_qty]
FROM ((((invoice_d LEFT JOIN invoice_h ON invoice_d.invoice_id =
invoice_h.invoice_id) LEFT JOIN item ON invoice_d.item_id = item.item_id)
LEFT JOIN item_types ON item.item_type = item_types.type_id) LEFT JOIN
customer ON invoice_h.customer_id = customer.customer_id) LEFT JOIN
customer_type ON customer.customer_type_id = customer_type.customer_type_id

If that works, you might be able to get a clue on how JET is understanding
it by opening the Immediate Window (Ctrl+G), and entering:
? TypeName(DLookup("Rate", "Query1"))
etc.

Also, if any of these separate fields displays left-aligned, Access is
understanding it as text (not numeric.)

Another option is to work with just invoice_d since all 3 fields in the
problem expression are from that "table". This might help identify the issue
if the problem were part of the join rather than the expression:

SELECT ([invoice_d]![price] *
(1 + CDbl(Nz([invoice_d]![discount],0))/100) *
[invoice_d]![ship_qty]) AS Extended_Gross
FROM invoice_d;

HTH

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

This fails even as a SELECT query.

SELECT customer_type.name, invoice_h.prebook_id, item_types.name,


Sum([invoice_d]![price]*((100+[invoice_d]![discount])/100)*[invoice_d]![ship_qty])
AS Extended_Gross,


Sum([invoice_d]![price]*[invoice_d]![ship_qty]) AS
Extended_Net
FROM ((((invoice_d LEFT JOIN invoice_h ON invoice_d.invoice_id =
invoice_h.invoice_id) LEFT JOIN item ON invoice_d.item_id = item.item_id)
LEFT JOIN item_types ON item.item_type = item_types.type_id) LEFT JOIN
customer ON invoice_h.customer_id = customer.customer_id) LEFT JOIN
customer_type ON customer.customer_type_id =
customer_type.customer_type_id
GROUP BY customer_type.name, invoice_h.prebook_id, item_types.name;

The isolated line is the problem child. Remove it, query fine.

I changed it to
CDbl(NZ(Sum(CDbl(NZ([invoice_d]![price],0))*((100+CDbl(NZ([invoice_d]![discount],0))/100))*CDbl(NZ([invoice_d]![ship_qty],0)))))

isolating every single piece and forcing it to a Double, as well as
forcing the whole thing as a double, same answer.

 

[Phil]

OK. Your test query, as is, works fine. Can't understand why the same
expression does not work in MY query. Tried it again, confirmed. If I
replace
[invoice_d]![discount]
with
CDbl(Nz([invoice_d]![discount],0))
my query still fails. They are all tables in MYSQL via ODBC, and both
Discount and Ship_Qty are decimal. Tried putting the Cdbl(NZ()) around
both of those, still fails.

I currently have:

SELECT
Sum([invoice_d]![price]*((100+CDbl(Nz([invoice_d]![discount],0)))/100)*CDbl(Nz([invoice_d]![ship_qty],0)))
AS Extended_Gross
FROM ((((invoice_d LEFT JOIN invoice_h ON invoice_d.invoice_id =
invoice_h.invoice_id) LEFT JOIN item ON invoice_d.item_id =
item.item_id) LEFT JOIN item_types ON item.item_type =
item_types.type_id) LEFT JOIN customer ON invoice_h.customer_id =
customer.customer_id) LEFT JOIN customer_type ON
customer.customer_type_id = customer_type.customer_type_id;


Tried your query to break the field down, it runs fine. WHen I try to
test it in the Immediate window, I get the Decimal Precision error.


Finally, If I remove the links to Item, Item_type, Customer, and
Customer_type,
IT WORKS.
Even if I take NO fields from any of these tables, Merely linking to any
fo them causes it it to fail. I checked all of the join fields, and
that are all Integer. SO the joins themsleves should be fine, and I am
taking NO data from any of those tables, but they still cause a failure.

So teh join appears to be the problem, yet I can not find anything wrong
with said join...

Okay, Phil. Now we have something specific to work with.

Let's try with just this one field, and no grouping, so we can be certain
where the problem lies:

SELECT ([invoice_d]![price] *
(1 + CDbl(Nz([invoice_d]![discount],0))/100) *
[invoice_d]![ship_qty]) AS Extended_Gross
FROM ((((invoice_d LEFT JOIN invoice_h ON invoice_d.invoice_id =
invoice_h.invoice_id) LEFT JOIN item ON invoice_d.item_id = item.item_id)
LEFT JOIN item_types ON item.item_type = item_types.type_id) LEFT JOIN
customer ON invoice_h.customer_id = customer.customer_id) LEFT JOIN
customer_type ON customer.customer_type_id = customer_type.customer_type_id

I have guessed that the discount is the one that is causing the issue, so
have forced it to double at the lowest level, and then handled it as
fractional values.

If that still fails, what are invoice_d and invoice_h? Are they queries?
Tables? Access tables? Linked dBase tables? ...?

If they are queries, you may need to perform the typecasting in the lower
queries. If tables, when you open in design view, what is the data type of
the price, discount, and ship_qty fields?

Another option is to try to break the field down further, i.e.:

SELECT [invoice_d]![price],
(1 + CDbl(Nz([invoice_d]![discount],0))/100) AS Rate,
[invoice_d]![ship_qty]
FROM ((((invoice_d LEFT JOIN invoice_h ON invoice_d.invoice_id =
invoice_h.invoice_id) LEFT JOIN item ON invoice_d.item_id = item.item_id)
LEFT JOIN item_types ON item.item_type = item_types.type_id) LEFT JOIN
customer ON invoice_h.customer_id = customer.customer_id) LEFT JOIN
customer_type ON customer.customer_type_id = customer_type.customer_type_id

If that works, you might be able to get a clue on how JET is understanding
it by opening the Immediate Window (Ctrl+G), and entering:
? TypeName(DLookup("Rate", "Query1"))
etc.

Also, if any of these separate fields displays left-aligned, Access is
understanding it as text (not numeric.)

Another option is to work with just invoice_d since all 3 fields in the
problem expression are from that "table". This might help identify the issue
if the problem were part of the join rather than the expression:

SELECT ([invoice_d]![price] *
(1 + CDbl(Nz([invoice_d]![discount],0))/100) *
[invoice_d]![ship_qty]) AS Extended_Gross
FROM invoice_d;

HTH

 

[Allen Browne]

I don't know the Scale and Precision of your Decimal fields, but the reason
I suggested the (1 + fraction) logic instead of (100 + value)/100 was in
case something is actually overflowing (as the error message suggests).

If your Decimal does not scale to overly many places, forcing Access to use
Currency math might be the best approach:

SELECT CCur(Sum(
CCur(Nz([invoice_d]![price],0)) *
CCur((1 + CCur(Nz([invoice_d]![discount],0)) / 100)) *
CCur(Nz([invoice_d]![ship_qty],0))
)) AS Extended_Gross

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

OK. Your test query, as is, works fine. Can't understand why the same
expression does not work in MY query. Tried it again, confirmed. If I
replace
[invoice_d]![discount]
with
CDbl(Nz([invoice_d]![discount],0))
my query still fails. They are all tables in MYSQL via ODBC, and both
Discount and Ship_Qty are decimal. Tried putting the Cdbl(NZ()) around
both of those, still fails.

I currently have:

SELECT
Sum([invoice_d]![price]*((100+CDbl(Nz([invoice_d]![discount],0)))/100)*CDbl(Nz([invoice_d]![ship_qty],0)))
AS Extended_Gross
FROM ((((invoice_d LEFT JOIN invoice_h ON invoice_d.invoice_id =
invoice_h.invoice_id) LEFT JOIN item ON invoice_d.item_id = item.item_id)
LEFT JOIN item_types ON item.item_type = item_types.type_id) LEFT JOIN
customer ON invoice_h.customer_id = customer.customer_id) LEFT JOIN
customer_type ON customer.customer_type_id =
customer_type.customer_type_id;


Tried your query to break the field down, it runs fine. WHen I try to
test it in the Immediate window, I get the Decimal Precision error.


Finally, If I remove the links to Item, Item_type, Customer, and
Customer_type,
IT WORKS.
Even if I take NO fields from any of these tables, Merely linking to any
fo them causes it it to fail. I checked all of the join fields, and that
are all Integer. SO the joins themsleves should be fine, and I am taking
NO data from any of those tables, but they still cause a failure.

So teh join appears to be the problem, yet I can not find anything wrong
with said join...

Okay, Phil. Now we have something specific to work with.

Let's try with just this one field, and no grouping, so we can be certain
where the problem lies:

SELECT ([invoice_d]![price] *
(1 + CDbl(Nz([invoice_d]![discount],0))/100) *
[invoice_d]![ship_qty]) AS Extended_Gross
FROM ((((invoice_d LEFT JOIN invoice_h ON invoice_d.invoice_id =
invoice_h.invoice_id) LEFT JOIN item ON invoice_d.item_id = item.item_id)
LEFT JOIN item_types ON item.item_type = item_types.type_id) LEFT JOIN
customer ON invoice_h.customer_id = customer.customer_id) LEFT JOIN
customer_type ON customer.customer_type_id =
customer_type.customer_type_id

I have guessed that the discount is the one that is causing the issue, so
have forced it to double at the lowest level, and then handled it as
fractional values.

If that still fails, what are invoice_d and invoice_h? Are they queries?
Tables? Access tables? Linked dBase tables? ...?

If they are queries, you may need to perform the typecasting in the lower
queries. If tables, when you open in design view, what is the data type
of the price, discount, and ship_qty fields?

Another option is to try to break the field down further, i.e.:

SELECT [invoice_d]![price],
(1 + CDbl(Nz([invoice_d]![discount],0))/100) AS Rate,
[invoice_d]![ship_qty]
FROM ((((invoice_d LEFT JOIN invoice_h ON invoice_d.invoice_id =
invoice_h.invoice_id) LEFT JOIN item ON invoice_d.item_id = item.item_id)
LEFT JOIN item_types ON item.item_type = item_types.type_id) LEFT JOIN
customer ON invoice_h.customer_id = customer.customer_id) LEFT JOIN
customer_type ON customer.customer_type_id =
customer_type.customer_type_id

If that works, you might be able to get a clue on how JET is
understanding it by opening the Immediate Window (Ctrl+G), and entering:
? TypeName(DLookup("Rate", "Query1"))
etc.

Also, if any of these separate fields displays left-aligned, Access is
understanding it as text (not numeric.)

Another option is to work with just invoice_d since all 3 fields in the
problem expression are from that "table". This might help identify the
issue if the problem were part of the join rather than the expression:

SELECT ([invoice_d]![price] *
(1 + CDbl(Nz([invoice_d]![discount],0))/100) *
[invoice_d]![ship_qty]) AS Extended_Gross
FROM invoice_d;