Decimal Precision error. GRRRR

  • Thread starter Thread starter Phil Smith
  • Start date Start date
P

Phil Smith

Is there any way to avoid this damn error?
Trying to convert using CDbl() does not work.
As best as I can tell, all of the data fits 8.4, which is the way the
field is configured, which means it is a bogus error.
This is a select query from a an ODBC connected table.
 
Is there any way to avoid this damn error?
Click to expand...

What's the error? You haven't said.
Trying to convert using CDbl() does not work.
Click to expand...

Trying to convert WHAT to CDbl()?
As best as I can tell, all of the data fits 8.4, which is the way the
field is configured, which means it is a bogus error.
This is a select query from a an ODBC connected table.
Click to expand...

In what database?

Please give us some help, Phil. We can't see your screen.

John W. Vinson[MVP]
 
John said:
What's the error? You haven't said.
Click to expand...

The error is usually, "The decimal field's precision is too small to
accept the numeric you attempted to add."

To reproduce:

CREATE TABLE Test (dec_col DECIMAL(8, 4) NOT NULL)
;
INSERT INTO Test (dec_col) VALUES (54321.1234)
;

The error means the integer portion (the value discarding the decimal
portion) puts the value outside the range of values the column allows
e.g. DECIMAL(8, 4) can accommodate values negative 9999.9999 to
positive 9999.9999.

The usual misconception is that DECIMAL(8, 4) means eight figures in
the integer portion and four figures in the decimal portion, whereas it
actually means eight figures in total with four figures in the decimal
portion so the integer portion will allow four figures (eight minus
four).

Jamie.

--
 
Sorry,..
"The decimal field's precision is too small to accept tghe numeric you
attempted to add."
The problem field is in a MYSQL DB, decimal type 8.4. Wrapping it in a
Cdbl() makes no difference.

Note, it does not happen whenever I use this field, not even when I pull
data using the exact same criteria. therefore getting the exact same
records. I have a report that pulls similar data, including this same
field, using the same criteria from thesame form, that works fine.
 
With previous versions of MySQL, that is what it meant: 8.4 meant 8
digits.4 digits. While the newest revision of MySQL conforms to 8.4
being 8 digits total it apparently allows less then 4 decimal points to
be borrowed by the whole number portion: 20,000 is valid. Access does
not like that. There actually is some data here that violates the
guidelines. I was able to get the maintainer of the database to bump it
up to 16.4, and the problem has gone away. I don't know why Access is
selective in enforcing this rule, but it works now. Thank you very much.

Phil
 
Phil said:
With previous versions of MySQL, that is what it meant: 8.4 meant 8
digits.4 digits.
Click to expand...

It sounds like MySQL was previously non-compliant with the SQL standard
as regards the DECIMAL type, at least in its information schema e.g.
failure to map the MySQL implementation-specific '8.4' to the standard
DECIMAL(12, 4).
While the newest revision of MySQL conforms to 8.4
being 8 digits total it apparently allows less then 4 decimal points to
be borrowed by the whole number portion: 20,000 is valid.

Access does
not like that.
Click to expand...

'Access' is not alone here: it sounds non-compliant (crazy, even <g>)
to me too! Rather than specifying the number of decimal places (as the
name DECIMAL would imply) you would actually be specifying the number
of significant figures.

Jamie.

--
 
Back
Top