Link to home
Start Free TrialLog in
Avatar of jn1480
jn1480Flag for United States of America

asked on

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

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!
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

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?
Avatar of jn1480

ASKER

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!
>>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?
Avatar of jn1480

ASKER

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

Avatar of jn1480

ASKER

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
SOLUTION
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of jn1480

ASKER

Thanks for the additional coaching Matthew - much appreciated!

Any thoughts as to a fix for my issue?
If you can reproduce the issue with a small sample database, attach here.
Make the Oracle tables local, with some representative data.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
Avatar of jn1480

ASKER

thanks!
Avatar of basefilm
basefilm

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!