Run-time error 3122 in strSQL statement 1

[vba317]

Hello,
I am getting an error 3122. You tried to execute a query that does not include the specified expression 'pfy' as part of an aggregate function. I have been working on this query for a couple of days now and I am hoping to get some help in resolving this error.


The results of my debug.print is :
INSERT INTO PROC_FYInfo(compid,compmne,compdesc,clntid,uci,clntname,rptpd,rptpddiff,fy,fydiff,fyord,MnthAsDt,mon,monfull,yr,MonShNm,MonFullNm,days,last3mondys,lastdayasdt,impfl) SELECT cl.compid,cmp.compmne,cmp.compdesc,fy.clntid,fy.uci,cl.clntname,fy.rptpd,fy.rptpddiff,fy.fy,fy.fydiff,fy.fyord,pd.monasdt,pd.mon_shnm,pd.mon_nm,fy.pfy,((pd.mon_shnm) & ' ' & (fy.fy)), ((pd.mon_nm) & ' ' & (fy.pfy)),pd.days,pd.last3mondys,pd.lastdayasdt,fy.imp FROM ((dbo_rpt_FYInfo fy INNER JOIN (dbo_rpt_Clients AS cl INNER JOIN dbo_dic_Company AS cmp ON cl.compid = cmp.compid) ON fy.clntid = cl.clntid) INNER JOIN dbo_dic_Period AS pd ON fy.rptpd = pd.pd) INNER JOIN PROC_ReportQueue AS rq ON fy.clntid = rq.clntid WHERE (((fy.clntid) = 37)) GROUP BY cl.compid,cmp.compmne,cmp.compdesc,fy.clntid,fy.uci,cl.clntname,fy.rptpd,fy.rptpddiff,fy.fy,fy.fydiff,fy.fyord,pd.monasdt,pd.mon_shnm,pd.mon_nm,((pd.mon_shnm) & ' ' & (fy.fy)),((pd.mon_nm) & ' ' & (fy.pfy)),pd.days,pd.last3mondys,pd.lastdayasdt,fy.imp,fy.fy ORDER BY cmp.compmne,fy.uci,fy.rptpd;

Tom

  strSQL = "INSERT INTO PROC_FYInfo(compid,compmne,compdesc,clntid,uci,clntname,rptpd,rptpddiff,fy,fydiff" & _
                                    ",fyord,MnthAsDt,mon,monfull,yr,MonShNm,MonFullNm,days,last3mondys,lastdayasdt,impfl) " & _
                        "SELECT cl.compid,cmp.compmne,cmp.compdesc,fy.clntid,fy.uci,cl.clntname,fy.rptpd,fy.rptpddiff" & _
                        ",fy.fy,fy.fydiff,fy.fyord,pd.monasdt,pd.mon_shnm,pd.mon_nm,fy.pfy" & _
                        ",((pd.mon_shnm) & ' ' & (fy.fy)), ((pd.mon_nm) & ' ' & (fy.pfy)),pd.days,pd.last3mondys,pd.lastdayasdt,fy.imp " & _
                    "FROM ((dbo_rpt_FYInfo fy " & _
                        "INNER JOIN (dbo_rpt_Clients AS cl " & _
                        "INNER JOIN dbo_dic_Company AS cmp ON cl.compid = cmp.compid) ON fy.clntid = cl.clntid) " & _
                        "INNER JOIN dbo_dic_Period AS pd ON fy.rptpd = pd.pd) INNER JOIN PROC_ReportQueue AS rq ON fy.clntid = rq.clntid " & _
                    "WHERE (((fy.clntid) = " & (liClntID) & ")) " & _
                    "GROUP BY cl.compid,cmp.compmne,cmp.compdesc,fy.clntid,fy.uci,cl.clntname,fy.rptpd,fy.rptpddiff,fy.fy" & _
                    ",fy.fydiff,fy.fyord,pd.monasdt,pd.mon_shnm,pd.mon_nm,((pd.mon_shnm) & ' ' & (fy.fy))" & _
                    ",((pd.mon_nm) & ' ' & (fy.pfy)),pd.days,pd.last3mondys,pd.lastdayasdt,fy.imp,fy.fy " & _
                    "ORDER BY cmp.compmne,fy.uci,fy.rptpd;"

 

[vba317]

I thought a little history is in order. I added a field to the table dbo_rpt_FYInfo called pfy. I wanted to change all references to the field from table dbo_dic_Period called pd.yr and change it to dbo_rpt_FYinfo field fy.pfy. The original query that works is:

 strSQL = "INSERT INTO PROC_FYInfo (compid,compmne,compdesc,clntid,uci,clntname,rptpd,rptpddiff,fy,fydiff" & _
                                    ",fyord,MnthAsDt,mon,monfull,yr,MonShNm,MonFullNm,days,last3mondys,lastdayasdt,impfl ) " & _
                        "SELECT cl.compid,cmp.compmne,cmp.compdesc,fy.clntid,fy.uci,cl.clntname,fy.rptpd,fy.rptpddiff" & _
                                    ",fy.fy,fy.fydiff,fy.fyord,pd.monasdt,pd.mon_shnm,pd.mon_nm,pd.yr" & _
                                    ",((pd.mon_shnm) & ' ' & (pd.yr)),((pd.mon_nm) & ' ' & (pd.yr)),pd.days,pd.last3mondys" & _
                                    ",pd.lastdayasdt,fy.imp " & _
                        "FROM ((dbo_rpt_FYInfo fy  " & _
                            "INNER JOIN (dbo_rpt_Clients cl " & _
                            "INNER JOIN dbo_dic_Company cmp ON cl.compid = cmp.compid) ON fy.clntid = cl.clntid) " & _
                            "INNER JOIN dbo_dic_Period pd ON fy.rptpd = pd.pd) " & _
                            "INNER JOIN PROC_ReportQueue rq ON fy.clntid = rq.clntid " & _
                        "WHERE (fy.clntid = " & (liClntID) & ") " & _
                        "GROUP BY cl.compid,cmp.compmne,cmp.compdesc,fy.clntid,fy.uci,cl.clntname,fy.rptpd,fy.rptpddiff,fy.fy" & _
                                    ",fy.fydiff,fy.fyord,pd.monasdt,pd.mon_shnm,pd.mon_nm,pd.yr,((pd.mon_shnm) & ' ' & (pd.yr))" & _
                                    ",((pd.mon_nm) & ' ' & (pd.yr)),pd.days,pd.last3mondys,pd.lastdayasdt,fy.imp " & _
                        "ORDER BY cmp.compmne,fy.uci,fy.rptpd;"

 

[vba317]

I just resolved the error by adding a fy.pfy to the end of the group by statement.

 

[Andrzejek]

Just an unrelated suggestion here...

When I write those looong SQLs and then try to Debug.Print, I have pretty much just a couple of long lines and never know if the lines are split at the end.

Consider this:
[pre]
strSQL = "INSERT INTO PROC_FYInfo (compid ... " & [blue]vbNewline[/blue] & _
" Values (...) " & [blue]vbNewline[/blue] & _
" Whatever ..."
[/pre]

A lot easier to read with Debug.Print, IMO

Have fun.

---- Andy

 

[vba317]

Thanks Andy for the suggestion. I plan on doing that in the future.

Tom