Overflow

[virtualkeeper via AccessMonster.com]

Does anyone know why I'm getting an overflow error here? I've taken care of
the divide by zero with IIF statements (I think).

SELECT CleanRoom.CleanRoomID, CleanRoom.Date, Year([Date]) AS [Year],
CleanRoom.CoatingID, CleanRoom.TotalVials, CleanRoom.CoatingWaste, CleanRoom.
HoursWorkedCoating, CleanRoom.Date2, CleanRoom.CleaningID, CleanRoom.
AmountMixed, CleanRoom.AmountCollected, CleanRoom.HoursWorkedCleaning, Sum(((
([TotalVials]*30)-[CoatingWaste])*0.034/128)*[CoatingVOC]) AS
CoatingVOCEmission, Sum(IIf([HoursWorkedCoating]=0,0,(((([TotalVials]*30)-
[CoatingWaste])*0.034/128)*[CoatingVOC])/[HoursWorkedCoating])) AS
CoatingVOCEmissionHour, Sum((((([TotalVials]*30)-[CoatingWaste])*0.034/128)*
[CoatingVOC])*0.0005) AS CoatingTonsYear, Sum((([AmountMixed]-
[AmountCollected])*0.034/128)*[CleaningVOC]) AS CleaningVOCEmission, Sum((((
[AmountMixed]-[AmountCollected])*0.034/128)*[CleaningVOC])/2000) AS
CleaningTonsYear, Sum(IIf([HoursWorkedCleaning]=0,0,((([AmountMixed]-
[AmountCollected])*0.034/128)*[CleaningVOC])/[HoursWorkedCleaning])) AS
CleaningVOCEmissionHours, Sum(((((([TotalVials]*30)-[CoatingWaste])*0.034/128)
*[CoatingVOC])/IIf([HoursWorkedCoating]=0,0,[HoursWorkedCoating]))+((((
[AmountMixed]-[AmountCollected])*0.034/128)*[CleaningVOC])/IIf(
[HoursWorkedCleaning]=0,0,[HoursWorkedCleaning]))) AS TotalVOCEmissionHour,
Sum((((([TotalVials]*30)-[CoatingWaste])*0.034/128)*[CoatingVOC])+(((
[AmountMixed]-[AmountCollected])*0.034/128)*[CleaningVOC])) AS
TotalVOCEmission, Sum(((((([TotalVials]*30)-[CoatingWaste])*0.034/128)*
[CoatingVOC])+((([AmountMixed]-[AmountCollected])*0.034/128)*[CleaningVOC]))
/2000) AS TonsYear
FROM (CoatingChemicals INNER JOIN CleanRoom ON CoatingChemicals.CoatingID =
CleanRoom.CoatingID) INNER JOIN CleaningChemicals ON CleanRoom.CleaningID =
CleaningChemicals.CleaningID
GROUP BY CleanRoom.CleanRoomID, CleanRoom.Date, Year([Date]), CleanRoom.
CoatingID, CleanRoom.TotalVials, CleanRoom.CoatingWaste, CleanRoom.
HoursWorkedCoating, CleanRoom.Date2, CleanRoom.CleaningID, CleanRoom.
AmountMixed, CleanRoom.AmountCollected, CleanRoom.HoursWorkedCleaning;

 

[John Spencer]

No, you haven't.

For example, in the following all you have done is say if HourWorkedCoating
= 0, then divide by 0, else divide by hoursWorkedCoating.

You have to cancel the division completely. For instance, divide by 1 as in
the following.
IIf([HoursWorkedCoating]=0,1,[HoursWorkedCoating])
Or
IIf([HoursWorkedCoating]=0,Null,[HoursWorkedCoating])

Or you have to Cancel the calculation by testing up front
Sum(IIF(HoursWorkedCoating = 0 or HoursWorkedCleaning = 0, 0, <<your big
calculation here>>))

Try building this up one step at a time. Do one of the calculations in one
of the calculated fields and if that works, add on the next bit. When it
breaks, then you will have some idea of what to fix.


, Sum(((((([TotalVials]*30)-[CoatingWaste])*0.034/128)*[CoatingVOC])/
////Still dividing by zero////
IIf([HoursWorkedCoating]=0,0,[HoursWorkedCoating]))

+(((([AmountMixed]-[AmountCollected])*0.034/128)*[CleaningVOC])/
////Still dividing by zero////
IIf([HoursWorkedCleaning]=0,0,[HoursWorkedCleaning]))) AS
TotalVOCEmissionHour,

 

[virtualkeeper via AccessMonster.com]

No, you haven't.

Thanks John. I thought I had and your first suggestion worked. What really
smacks my forehead is that I did just that Friday, using both 1 and Null and
it didn't work. Now it does so I'm not going to argue with success. I tried
doing it step by step as you suggested by stripping everything else out of
the table and that helped to. I really thank you for your guidance and
patience. Have a good one.