Re: Decimal fields's precision is too small to accept the numeric you attempted to add

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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Phil" <phil_at_nhs@xxxxxxxxxxx> wrote in message
news:e8ReIl9OGHA.1180@xxxxxxxxxxxxxxxxxxxxxxx
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;


.

Relevant Pages

  • Re: Unparsable Record
    ... I tried simply importing 1 column - deleting all the rest. ... Method 'ExecuteTempImexSpec' of object '_WizHook' failed error message. ... If that fails, delete 11 more columns so only the first 11 ... importing the first column - skipping all the other columns, ...
    (microsoft.public.access.externaldata)
  • RE: Problem with SetFocus
    ... An Engineers Prayer: ... when all else fails, ... I tried OnError Resume Next to suppress the Error message and now it works ... the error message suppressing. ...
    (microsoft.public.access.modulesdaovba)
  • Re: MOND
    ... smaller scale objects. ... A recent review of data for 60 galaxies (in ... Dark matter may address the general trends but it cannot ... DM fails to pass all galactic test at once: ...
    (sci.physics.relativity)
  • Re: Closing Form Produces "Runtime Error 2585"
    ... My main concern is that you should NEVER store an easily calculatable entity ... calculated field to give you the Full Name from its component parts as and ... > I'm getting the above referenced error (error message: ... DoCmd, it still fails. ...
    (microsoft.public.access.formscoding)
  • Re: Permanently Disabling Content Advisor
    ... If that fails, post the exact error message to a newsgroup about IE. ...
    (microsoft.public.security)