Re: Decimal fields's precision is too small to accept the numeric you attempted to add
- From: Phil <phil_at_nhs@xxxxxxxxxxx>
- Date: Mon, 27 Feb 2006 12:06:20 -0800
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...
Allen Browne wrote:
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
- Follow-Ups:
- References:
- Decimal fields's precision is too small to accept the numeric you attempted to add
- From: Phil
- Re: Decimal fields's precision is too small to accept the numeric you attempted to add
- From: Allen Browne
- Re: Decimal fields's precision is too small to accept the numeric you attempted to add
- From: Phil
- Re: Decimal fields's precision is too small to accept the numeric you attempted to add
- From: Allen Browne
- Re: Decimal fields's precision is too small to accept the numeric you attempted to add
- From: Phil
- Re: Decimal fields's precision is too small to accept the numeric you attempted to add
- From: Allen Browne
- Decimal fields's precision is too small to accept the numeric you attempted to add
- Prev by Date: Re: Query to select first record of changes between records
- Next by Date: Re: "Create a new query" option has disappeared, where is it?
- Previous by thread: Re: Decimal fields's precision is too small to accept the numeric you attempted to add
- Next by thread: Re: Decimal fields's precision is too small to accept the numeric you attempted to add
- Index(es):
Relevant Pages
|
