Correct syntax for "Where/Having" to stop aggregate Error 1

[GPM4663]

Hi Everyone,
I have the following query:

SELECT tblJobCardProcesses.jobCardRef
FROM tblJobCardProcesses
WHERE (((tblJobCardProcesses.workcentreStop) Is Null)) OR (((tblJobCardProcesses.workcentreStop) Is Not Null))
GROUP BY tblJobCardProcesses.jobCardRef
HAVING (((Sum(CLng([stage])))=5)) OR (((Sum(CLng([stage])))=0));

That I would like to change the criteria so that it is ((workcentrestop) Is Null AND SUM(Clng([stage]))=5) OR
((workcentrestop) Is Not Null AND SUM(Clng([stage]))=0)

Everytime I try it I get an aggregate error and I just can't get the syntax right. If I try to do it on two lines in the design view it keeps converting it back to the original. Could anyone please help me.

Thanks in advance for any help,

GPM

 

[PHV]

SELECT jobCardRef, Sum(CLng([stage]))
FROM tblJobCardProcesses
GROUP BY jobCardRef
HAVING (workcentreStop Is Null AND Sum(CLng([stage]))=5) OR (NOT (workcentreStop Is Null) AND Sum(CLng([stage]))=0)

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

 

[GPM4663]

Thanks for the response, Unfortunately I'm getting the following error:

"You tried to execute a query that does not include the specified expression 'Sum(CLng([stage]))=5) AND workcentreStop Is Null OR Sum(CLng([stage]))=0) AND NOT (workcentreStop Is Null)' as part of an aggregate function"

Is there something we can do to tweak this?

thanks

GPM

 

[PHV]

What is your actual SQL code raising the above error ?

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

 

[GPM4663]

Hi PHV,
Here is the SQL:

SELECT tblJobCardProcesses.jobCardRef, Sum(CLng([stage])) AS Expr1
FROM tblJobCardProcesses
GROUP BY tblJobCardProcesses.jobCardRef
HAVING (((Sum(CLng([stage])))=5) AND ((tblJobCardProcesses.workcentreStop) Is Null)) OR (((Sum(CLng([stage])))=0) AND (Not (tblJobCardProcesses.workcentreStop) Is Null));

Thanks for all the help so far,

GPM

 

[PHV]

You may try this:
SELECT tblJobCardProcesses.jobCardRef, Sum(CLng([stage])) AS Expr1, (tblJobCardProcesses.workcentreStop Is Null) AS Expr2
FROM tblJobCardProcesses
GROUP BY tblJobCardProcesses.jobCardRef, (tblJobCardProcesses.workcentreStop Is Null)
HAVING (Sum(CLng([stage]))=5 AND tblJobCardProcesses.workcentreStop Is Null) OR (Sum(CLng([stage]))=0 AND Not (tblJobCardProcesses.workcentreStop Is Null))

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

 

[GPM4663]

Hi PHV,
That has worked a treat, thanks for all the help.

Cheers,

GPM