Aggregate Function

Hi all. I've tried finding an answer to this and can't find one somewhat
close. I'm getting an "You tried to execute a query that does not include the
specified expression name as part of an aggregate function." error . I've
put in some IIf statements to prevent Overflow or the Can Not Divide by Zero
however now said Function problem exists. Why am I getting this issue? Any
help is very much appreciated.

Here's the drill down on the SQL:

Sum (IIf([HoursWorkedCoating]=0,0,((((([TotalVials]*30)-[CoatingWaste])*0.
034/128)*[CoatingVOC])/[HoursWorkedCoating])+(((([AmountMixed]-
[AmountCollected])*0.034/128)*[CleaningVOC])/IIf([HoursWorkedCleaning]=0,0,
[HoursWorkedCleaning])))) AS TotalVOCEmissionHour,

and here is the whole Query if needed:

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, IIf ([HoursWorkedCoating]=0,0,Sum( ((([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, IIf ([HoursWorkedCleaning]=0,0,Sum(((([AmountMixed]-
[AmountCollected])*0.034/128)*[CleaningVOC])/[HoursWorkedCleaning])) AS
CleaningVOCEmissionHours, Sum (IIf([HoursWorkedCoating]=0,0,((((([TotalVials]
*30)-[CoatingWaste])*0.034/128)*[CoatingVOC])/[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;

--
Later,

Darrin

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200606/1

Try moving the IIF inside the Sum functions. because of the Grouping, it is
looking for either a group field or an aggregate function. j.

"virtualkeeper via AccessMonster.com" wrote:

Hi all. I've tried finding an answer to this and can't find one somewhat
close. I'm getting an "You tried to execute a query that does not include the
specified expression name as part of an aggregate function." error . I've
put in some IIf statements to prevent Overflow or the Can Not Divide by Zero
however now said Function problem exists. Why am I getting this issue? Any
help is very much appreciated.

Here's the drill down on the SQL:

Sum (IIf([HoursWorkedCoating]=0,0,((((([TotalVials]*30)-[CoatingWaste])*0.
034/128)*[CoatingVOC])/[HoursWorkedCoating])+(((([AmountMixed]-
[AmountCollected])*0.034/128)*[CleaningVOC])/IIf([HoursWorkedCleaning]=0,0,
[HoursWorkedCleaning])))) AS TotalVOCEmissionHour,

and here is the whole Query if needed:

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, IIf ([HoursWorkedCoating]=0,0,Sum( ((([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, IIf ([HoursWorkedCleaning]=0,0,Sum(((([AmountMixed]-
[AmountCollected])*0.034/128)*[CleaningVOC])/[HoursWorkedCleaning])) AS
CleaningVOCEmissionHours, Sum (IIf([HoursWorkedCoating]=0,0,((((([TotalVials]
*30)-[CoatingWaste])*0.034/128)*[CoatingVOC])/[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;

--
Later,

Darrin

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200606/1

Jen wrote:
Try moving the IIF inside the Sum functions. because of the Grouping, it is
looking for either a group field or an aggregate function. j.


Thanks Jen, that did the trick for the error message but I'm not back at my
overflow problem due to dividing by zero.

--
Later,

Darrin

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200606/1

Sorry, typo there. Meant to say: Thanks Jen, that did the trick for the
error message but I'm now back at my
overflow problem. Don't know what the overflow is. It looks like I've taken
care of the divide by zero problem.

virtualkeeper wrote:
Try moving the IIF inside the Sum functions. because of the Grouping, it is
looking for either a group field or an aggregate function. j.

Thanks Jen, that did the trick for the error message but I'm not back at my
overflow problem due to dividing by zero.


--
Later,

Darrin

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200606/1