Having clause error 1

[vba317]

I am using access 2003 and I am getting a having clause error. I believe one of the variables in the having clause is probably null. I tried to use ?proctot in the immediate window but nothing came up. I know how to trap for this error after the strSQL is created (If Not rstCPT.EOF ) but how can I resolve this error? I have highlighted the code causing the error in blue. Any help is appreciated.

strSQL = "SELECT cptdisplay,Max(cptdesc) as dsc " & _
            "FROM PROC_RptSrc_CPTSumm_Chgs " & _
            "HAVING ((Sum(proctot)+Sum(chg)) <> 0) " & _
            "GROUP BY cptdisplay " & _
            "ORDER BY cptdisplay;"
[Blue] Set rstCPT = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)[/Blue]
If Not rstCPT.EOF Then
    With rstCPT
        .MoveLast
        .MoveFirst
    End With

 

[ClulessChris]

Missing parentheses?

"HAVING ((Sum(proctot)+Sum(chg)) <> 0) " & _

should it be

"HAVING (((Sum(proctot)+Sum(chg)) <> 0) " & _

Never knock on Death's door: ring the bell and run away! Death really hates that!

 

[ClulessChris]

Forget that I missrad it, sorry

Never knock on Death's door: ring the bell and run away! Death really hates that!

 

[Andrzejek]

It is not your blue line of code that errors, it is your strSQL.

When the error happens, what's the value in strSQL?
Copy it and paste to your Access and try to run it.

If you suspect the proctot to be NULL, use NZ to replace the NULL with 0 - if that's the sollution

Have fun.

---- Andy

 

[PHV]

The HAVING clause should be AFTER the GROUP BY clause !

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

 

[vba317]

I tried to copy and paste the code but because of the "" and the & that didnt work out to well. So I created e query that would do the same thing as the strSQL. The query worked perfectly and all the fields did have data in them. By the way the error is 3133 Syntax error in HAVING clause. Any help is appreciated

This is the SQL of the query that I wrote:

SELECT PROC_RptSrc_CPTSumm_Chgs.uci, PROC_RptSrc_CPTSumm_Chgs.rptpd, PROC_RptSrc_CPTSumm_Chgs.cptdisplay, Max(PROC_RptSrc_CPTSumm_Chgs.cptdesc) AS MaxOfcptdesc, Sum(PROC_RptSrc_CPTSumm_Chgs.proctot) AS SumOfproctot, Sum(PROC_RptSrc_CPTSumm_Chgs.chg) AS SumOfchg
FROM PROC_RptSrc_CPTSumm_Chgs
GROUP BY PROC_RptSrc_CPTSumm_Chgs.uci, PROC_RptSrc_CPTSumm_Chgs.rptpd, PROC_RptSrc_CPTSumm_Chgs.cptdisplay
HAVING (((PROC_RptSrc_CPTSumm_Chgs.uci)="WMG") AND ((PROC_RptSrc_CPTSumm_Chgs.rptpd)=374) AND ((Sum(PROC_RptSrc_CPTSumm_Chgs.proctot))<>0) AND ((Sum(PROC_RptSrc_CPTSumm_Chgs.chg))<>0));

 

[PHV]

Again, in your VBA code the HAVING clause is in the wrong place.
Did you make sense with my post timestamped 19 Mar 13 9:41 ?

 

[Andrzejek]

Those are 2 different SQLs:
[tt]
SELECT PROC_RptSrc_CPTSumm_Chgs.uci,
PROC_RptSrc_CPTSumm_Chgs.rptpd,
PROC_RptSrc_CPTSumm_Chgs.cptdisplay,
Max(PROC_RptSrc_CPTSumm_Chgs.cptdesc) AS MaxOfcptdesc,
Sum(PROC_RptSrc_CPTSumm_Chgs.proctot) AS SumOfproctot,
Sum(PROC_RptSrc_CPTSumm_Chgs.chg) AS SumOfchg
FROM PROC_RptSrc_CPTSumm_Chgs
[blue]GROUP BY[/blue] PROC_RptSrc_CPTSumm_Chgs.uci,
PROC_RptSrc_CPTSumm_Chgs.rptpd,
PROC_RptSrc_CPTSumm_Chgs.cptdisplay
[red]HAVING[/red] (((PROC_RptSrc_CPTSumm_Chgs.uci)="WMG")
AND ((PROC_RptSrc_CPTSumm_Chgs.rptpd)=374)
AND ((Sum(PROC_RptSrc_CPTSumm_Chgs.proctot))<>0)
AND ((Sum(PROC_RptSrc_CPTSumm_Chgs.chg))<>0));

SELECT cptdisplay,Max(cptdesc) as dsc
FROM PROC_RptSrc_CPTSumm_Chgs
[red]HAVING[/red] ((Sum(proctot)+Sum(chg)) <> 0)
[blue]GROUP BY[/blue] cptdisplay
ORDER BY cptdisplay;[/tt]

And - as you can see - HAVING and GROUP BY are switched
(as PHV suggested)

BTW, you can get the value of strSQL by typing in the Immediate Window:
? strSQL

Have fun.

---- Andy

 

[vba317]

PHV ,
Sorry I had not refreshed my screen before I sent my last email. So I didn't see your response. I put the having clause at the bottom. Than I couldn't get the group by clause to work so I took it out . The query works fine now. Thanks for your help.