Runtime Errror 6 Overflow when running make table query

[bitech]

Here's my code

Function outliers()
'create outliers report
DoCmd.setwarnings False
DoCmd.RunSQL "SELECT Skills.Type, Employees.FirstOfKey, Sum(YTD.[ACD CALLS]) AS NCH, Sum([ACD AHT]*[ACD CALLS]) AS Total_AHT, Sum([ATT]*[ACD CALLS]) AS Total_ATT, Sum([ACW]*[ACD CALLS]) AS Total_ACW, Sum([HOLD]*[ACD CALLS]) AS Total_HOLD, YTD.DATE INTO tmp_Outliers FROM (YTD INNER JOIN Employees ON YTD.Key = Employees.FirstOfKey) INNER JOIN Skills ON YTD.[Split/Skill] = Skills.Skill GROUP BY Skills.Type, Employees.FirstOfKey, YTD.DATE HAVING (((YTD.DATE)=Date()-1));"

<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<This is where the code stops and I get the overflow message>>>>>>>>>>>>>>


'ACW
DoCmd.RunSQL "SELECT tmp_Outliers.Type, tmp_Outliers.FirstOfKey, tmp_Outliers.NCH, [Total_AHT]/[NCH] AS AHT, [Total_ACW]/[NCH] AS ACW, [Total_ATT]/[NCH] AS ATT, [Total_HOLD]/[NCH] AS HOLD, tmp_Outliers.DATE, Employees.Site, 'Team ' & Employees_1.Last AS Supervisor INTO ACW FROM (tmp_Outliers INNER JOIN Employees ON tmp_Outliers.FirstOfKey = Employees.FirstOfKey) INNER JOIN Employees AS Employees_1 ON Employees.ReportsTo = Employees_1.AIN WHERE ((([Total_ACW]/[NCH])>90));"
'AHT
DoCmd.RunSQL "SELECT tmp_Outliers.Type, tmp_Outliers.FirstOfKey, tmp_Outliers.NCH, [Total_AHT]/[NCH] AS AHT, [Total_ACW]/[NCH] AS ACW, [Total_ATT]/[NCH] AS ATT, [Total_HOLD]/[NCH] AS HOLD, tmp_Outliers.DATE, Employees.Site, 'Team ' & Employees_1.Last AS Supervisor INTO AHT FROM (tmp_Outliers INNER JOIN Employees ON tmp_Outliers.FirstOfKey = Employees.FirstOfKey) INNER JOIN Employees AS Employees_1 ON Employees.ReportsTo = Employees_1.AIN WHERE ((([Total_AHT]/[NCH])>475));"
'ATT
DoCmd.RunSQL "SELECT tmp_Outliers.Type, tmp_Outliers.FirstOfKey, tmp_Outliers.NCH, [Total_AHT]/[NCH] AS AHT, [Total_ACW]/[NCH] AS ACW, [Total_ATT]/[NCH] AS ATT, [Total_HOLD]/[NCH] AS HOLD, tmp_Outliers.DATE, Employees.Site, 'Team ' & Employees_1.Last AS Supervisor INTO ATT FROM (tmp_Outliers INNER JOIN Employees ON tmp_Outliers.FirstOfKey = Employees.FirstOfKey) INNER JOIN Employees AS Employees_1 ON Employees.ReportsTo = Employees_1.AIN WHERE ((([Total_ATT]/[NCH])>350));"
'HOLD
DoCmd.RunSQL "SELECT tmp_Outliers.Type, tmp_Outliers.FirstOfKey, tmp_Outliers.NCH, [Total_AHT]/[NCH] AS AHT, [Total_ACW]/[NCH] AS ACW, [Total_ATT]/[NCH] AS ATT, [Total_HOLD]/[NCH] AS HOLD, tmp_Outliers.DATE, Employees.Site, 'Team ' & Employees_1.Last AS Supervisor INTO HOLD FROM (tmp_Outliers INNER JOIN Employees ON tmp_Outliers.FirstOfKey = Employees.FirstOfKey) INNER JOIN Employees AS Employees_1 ON Employees.ReportsTo = Employees_1.AIN WHERE ((([Total_HOLD]/[NCH])>120));"
MsgBox "Done"
DoCmd.setwarnings True
End Function


Thanks in a advance for the help

 

[grgimpy]

Many times Error 6: Overflow is caused by trying to store data in the wrong data type. Maybe you are trying to store data from one table into a field that is of a different data type in the other table.

Another way this error will come up is if you divide by zero in your calculations.

You might want to look at some of the fields you multiplying together. What type of fields are trying to store this data into? Maybe it is the wrong type.

Here's as link that doesn't directly talk about your problem but may be useful:

http://www.applecore99.com/gen/gen059.asp

 

[PHV]

I'd try the CDbl function.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[missinglinq]

Actually the problem is not usually one of Datatype being wrong, but rather of the Field Size being set incorrectly for a Number Datatype . Trying to load data, such as the result of a calculation into a Number Datatype whose Field Size is too small to hold it. If the field is defined with Field Size of Integer and the results is over 32,676 (the limit for an Integer) you'll get an Overflow error. Hence PHV's suggestion to use CDbl function to coerce the accepting field to accept a larger sized number than it's designed to accept.

These types of things can be particularly difficult to pinpoint when they appear in queries used for running things like Year-to-Date reports. They often run fine for months until the point is reached when the Year-to-Date running totals suddenly exceed the limit of the defined Field Size.

BTW, division by Zero throws Error 11, Division by Zero, rather than Error 6, Overflow.

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!