Overflow Error

[laurenh5516]

I have a query that has always worked, but now does not work and I get an Overflow error. I read that it has to do with dividing by zero or null values, but when I try to remove nulls or use criteria >0 I get an error that says "Your query does not include the specified expression'qry_ALPS_P6Earned.EarnedHrs>0' as part of an aggregate function. Here is my query below:

SELECT qry_ALPS_Actuals.AreaLead, qry_ALPS_Actuals.Sub_PJN, Sum(qry_ALPS_ConstructionEst.SumofTotal) AS EstTotal, Sum(qry_ALPS_Actuals.TotalCost) AS ActualCost, qry_ALPS_P6Earned.SumOfTotalHoursDirect AS PlannedHrs, qry_ALPS_P6Earned.EarnedHrs, qry_ALPS_P6Earned.SumOfRemainingTotalDirect AS RemainingHrs, Sum([EarnedHrs]/[SumOfTotalHoursDirect]) AS PercCom, Abs(Sum([EarnedHrs]/[SumOfTotalHoursDirect])*Sum([SumofTotal])-Sum([TotalCost])) AS OverSpend

FROM tbl_MasterProjList INNER JOIN ((qry_ALPS_Actuals LEFT JOIN qry_ALPS_ConstructionEst ON (qry_ALPS_Actuals.AreaLead = qry_ALPS_ConstructionEst.AreaLead) AND (qry_ALPS_Actuals.Sub_PJN = qry_ALPS_ConstructionEst.Sub_PJN)) LEFT JOIN qry_ALPS_P6Earned ON (qry_ALPS_Actuals.Sub_PJN = qry_ALPS_P6Earned.Sub_PJN) AND (qry_ALPS_Actuals.AreaLead = qry_ALPS_P6Earned.AreaLead)) ON tbl_MasterProjList.SUB_PJN = qry_ALPS_Actuals.Sub_PJN

WHERE (((tbl_MasterProjList.Status)="Active"))

GROUP BY qry_ALPS_Actuals.AreaLead, qry_ALPS_Actuals.Sub_PJN, qry_ALPS_P6Earned.SumOfTotalHoursDirect, qry_ALPS_P6Earned.EarnedHrs, qry_ALPS_P6Earned.SumOfRemainingTotalDirect

HAVING (((qry_ALPS_P6Earned.EarnedHrs)>0))

ORDER BY qry_ALPS_Actuals.AreaLead, Abs(Sum([EarnedHrs]/[SumOfTotalHoursDirect])*Sum([SumofTotal])-Sum([TotalCost])) DESC;

 

[ranman256]

Your target field is too small.
use LONG ,or DOUBLE.

 

[laurenh5516]

What is a target field?

 

[xenou]

Since you are left joining to qry_ALPS_P6Earned it is not possible to exclude nulls (otherwise, you would not use a left join in the first place). The solution may be to avoid nulls or zeros (since you should never include nulls in a sum and you should never have zero in a divisor) but you will have to think very carefully about how you do this since you have a complex query here and your sum that you are trying to fix is only one part of it.

To debug this you might want to first look at what is in the results without grouping and see what these nulls actually are and whether you can safely filter them out without affecting the other parts of the query.