Re: Decimal Precision error. GRRRR

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





Jamie Collins wrote:
John Vinson wrote:

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?

What's the error? You haven't said.


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.

--

.

Relevant Pages