Aggregate Function

[virtualkeeper via AccessMonster.com]

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;

 

[Jen]

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.

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;

 

[virtualkeeper via AccessMonster.com]

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.

 

[virtualkeeper via AccessMonster.com]

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.