We help IT Professionals succeed at work.

Access 2007 "the decimal field's precision is too small"

Medium Priority
10,210 Views
Last Modified: 2012-10-11
We are running a Make Table query that pulls from two Oracle tables.
It has always run without error every month, but this month no luck.

We receive this error in a pop-up window:
=========
Microsoft Visual Basic

Run-time error '3761':

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

[Continue]   [End]   [Debug]   [Help]
=========

That's the error we receive when it is running via the VBA that we usually run to execute everything in order.

The following, simpler error comes up if running manually, just the single query:
=========
Microsoft Office Access

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

[OK]   [Help]
=========

Any thoughts?
Thanks!
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2010

Commented:
Sounds like a data type problem.

For example, if you have a column specified as decimal(7, 3), the moment you try to insert a value of 10,000 into that column you will get this error, because the data type is only allowing for 4 significant digits to the left of the decimal point.

What are the data types of the columns involved, and what sort of operations are you using in your make-table query?

Author

Commented:
I am confused... (yes, a bit of a newbie here...)
I am doing a Make Table - the table doesn't exist, therefore, how cna I specify data types for my columns?

I am trying to pull the SQL of the query here in a minute - standby ;)
Thanks Matthew!
CERTIFIED EXPERT
Top Expert 2010

Commented:
>>I am doing a Make Table - the table doesn't exist, therefore, how cna I specify data types for my columns?

What are the data types for the columns in the table(s) your make-table refers to?

Author

Commented:
This is the SQL of the query.
We used the Design View in MSAccess to build it, so the SQL means pretty much squat to me...

We are pulling in two linked tables that reside in Oracle.
And then a third local table that simply contains dates, etc...

You can see we are doing some math, sum-ing, etc... but nothing major I don't think?
SELECT [ARGPRD_EMPLOYEE-Active].STORE, SYSTEM_ASSOC_WEEKLY_HIST.EMPLOYEE, [ARGPRD_EMPLOYEE-Active].NAME, ParmWeeks.WkCode, ParmWeeks.WkTitle, Sum(SYSTEM_ASSOC_WEEKLY_HIST.TOTAL_SALES) AS Sales, Sum(SYSTEM_ASSOC_WEEKLY_HIST.TOTAL_TRANS) AS Trans, IIf([TRANS] Is Null Or [TRANS]=0,Null,CLng((Sum(SYSTEM_ASSOC_WEEKLY_HIST.TOTAL_SALES)/Sum(SYSTEM_ASSOC_WEEKLY_HIST.TOTAL_TRANS))*100)/100) AS ADS, Sum(SYSTEM_ASSOC_WEEKLY_HIST.TOTAL_UNITS) AS Units, IIf([TRANS] Is Null Or [TRANS]=0,Null,CLng((Sum(SYSTEM_ASSOC_WEEKLY_HIST.TOTAL_UNITS)/Sum(SYSTEM_ASSOC_WEEKLY_HIST.TOTAL_TRANS))*100)/100) AS UPT, Sum(SYSTEM_ASSOC_WEEKLY_HIST.HOURS) AS Hours, IIf([HOURS] Is Null Or [HOURS]=0 Or [SALES] Is Null Or [SALES]=0,Null,CLng((Sum(SYSTEM_ASSOC_WEEKLY_HIST.TOTAL_SALES)/Sum(SYSTEM_ASSOC_WEEKLY_HIST.HOURS))*100)/100) AS SPH, Sum(SYSTEM_ASSOC_WEEKLY_HIST.CC_APPS) AS CC_Apps, Sum(SYSTEM_ASSOC_WEEKLY_HIST.TOTAL_TRANS)/Sum(SYSTEM_ASSOC_WEEKLY_HIST.CC_APPS) AS CC_AppConversion, IIf([NBR_FTE]=1,"FT","PT") AS Status INTO [Stats-Assoc]
FROM ParmWeeks, SYSTEM_ASSOC_WEEKLY_HIST INNER JOIN [ARGPRD_EMPLOYEE-Active] ON (SYSTEM_ASSOC_WEEKLY_HIST.EMPLOYEE = [ARGPRD_EMPLOYEE-Active].EMPLOYEE) AND (SYSTEM_ASSOC_WEEKLY_HIST.STORE = [ARGPRD_EMPLOYEE-Active].STORE)
WHERE (((([FISCAL_YR]*100)+[FISCAL_WK]) Between [WkIdntFrom] And [WkIdntTo]) AND ((ParmWeeks.WkCode)<=4))
GROUP BY [ARGPRD_EMPLOYEE-Active].STORE, SYSTEM_ASSOC_WEEKLY_HIST.EMPLOYEE, [ARGPRD_EMPLOYEE-Active].NAME, ParmWeeks.WkCode, ParmWeeks.WkTitle, IIf([NBR_FTE]=1,"FT","PT");

Open in new window

Author

Commented:
All fields in linked table are Number (Decimal) fields with the Precision (?? - not sure what this means) field set to numbers like 2, 4, 9, etc
CERTIFIED EXPERT
Top Expert 2010
Commented:
A decimal data type has a precision and a scale.  Precision is the maximum number of significant figures that can be stored, and the scale indicates how many of those are reserved for decimal places.

For example, if the precision = 5 and the scale = 0, then the maximum value that can be stored is 99999, and no decimal places will be stored.

If the precision = 5 and the scale = 2, then the maximum value that can be stored is 999.99.  (Of the 5 significant figures, two are reserved for decimal places, thus leaving 3 for the 'integer' portion of the number.)

Author

Commented:
Thanks for the additional coaching Matthew - much appreciated!

Any thoughts as to a fix for my issue?
Hamed NasrRetired IT Professional
CERTIFIED EXPERT

Commented:
If you can reproduce the issue with a small sample database, attach here.
Make the Oracle tables local, with some representative data.
CERTIFIED EXPERT
Top Expert 2010
Commented:
One option might be to have your Oracle DBA alter the back-end tables to use a higher precision value.

Another might be to tweak your query to use CDbl to force type conversions to the more liberal Double data type.  For example, you could replace every reference to SYSTEM_ASSOC_WEEKLY_HIST.TOTAL_SALES with CDbl(SYSTEM_ASSOC_WEEKLY_HIST.TOTAL_SALES), and so on.
One solution would be to use the table once it is created by the make table query after it is modified formatting the fields using SYSTEM_ASSOC_WEEKLY_HIST.TOTAL_SALES to Double or Decimal(18, 9) or whatever precision is required. Then change the make table query to an append query to see if that would work
Commented:
Thanks for all the help guys - turned out to be an issue with the linked table.

A DBA had changed the field size of one of the fields from 4 to six digits:
=========
Field Size: Decimal ; Precision: 6 ; Scale: 0
--WAS--
Field Size: Decimal ; Precision: 4 ; Scale: 0
=========

Turns out Access can't handle that kind of change without deleting and re-adding the linked table.
Works like a charm after deleting/re-adding the linked table.

I am awarding points to matthewspatrick for his add'l coaching and advice.
Gained some valuable learning from his notes, that made me sound a little less stupid when discussing with my DBA - thanks!
CERTIFIED EXPERT
Top Expert 2010

Commented:
Glad to help :)
It happens when the front end doesn't know what the back end is doing and vice versa. Glad the problem is solved.

Author

Commented:
thanks!

Commented:
I ran into this same issue, found the discussion helpful and thought I'd post my notes for the next guy...

Using Access 2010 with a linked database connection to an Oracle 11g database.
Using the Access GUI I created a query where one column was:
    IM_EFC: [RCRAPP2_IM_1_SC]+[RCRAPP2_IM_1_PC]
(in english, the column name was to be 'IM_EFC' and it was to consist of the sum of the two field names specified).

When I ran the query in just 'Select' mode, it worked, no problem.  When I ran it in 'Make Table' mode though, I'd just get that "The decimal field's precision is too small to accept the numeric you attempted to add." error.

As an aside, my query had about 30+ columns, and the dumb error message doesn't give any hint as to which one was causing the problem.  To find out I copied the query, then deleted columns one-by-one from the copy until the error went away.  Anyway...

After reading this thread I changed the column from what I noted above to:
    IM_EFC: CDbl([RCRAPP2_IM_1_SC]+[RCRAPP2_IM_1_PC])

And that fixed it.  Thanks everyone!