runtime error 3122

[Gregorys05]

Hi Guy's
I have the below SQL query and i keep getting runtime error 3122

You tried to execute a query that doesn't include the specified expression as part of an aggregate function.

can any one please help

Private Sub Form_Open(Cancel As Integer)

Dim xxx As String

Dim sql As String

If Forms![frmContractFileUploadNew(SG Testing)]!optPropertyType = 1 Then xxx = "Not Like " Else xxx = "Like "

sql = "SELECT tblPlannedWork.fldContractorCode, tlkpYear.fldFinancialYear, tblReportingPeriod.fldMonth, tblElement.fldElement, Count(tblPlannedWork.fldPropertyRef) AS [Total Elements Complete] " & _
" FROM ((tblPlannedWork INNER JOIN tblElement ON tblPlannedWork.fldElementID = tblElement.fldElementID) INNER JOIN tblReportingPeriod ON tblPlannedWork.fldReportingPeriod = tblReportingPeriod.fldReportingPeriodCode) INNER JOIN tlkpYear ON tblReportingPeriod.fldYear = tlkpYear.fldYear " & _
" WHERE (((tblPlannedWork.fldCAStatusID) In (2,3)) AND ((tblReportingPeriod.fldYear) In (select [fldCurrentYear] FROM [tlkpCurrentYear]) Or (tblReportingPeriod.fldYear)=2009)) OR (((tblReportingPeriod.fldYear) In (select [fldCurrentYear] FROM [tlkpCurrentYear]) Or (tblReportingPeriod.fldYear)=2009) AND ((tblPlannedWork.fldContractorCode)='one')) " & _
" GROUP BY tblPlannedWork.fldContractorCode, tlkpYear.fldFinancialYear, tblReportingPeriod.fldMonth, tblElement.fldElement, tblReportingPeriod.fldYear, tblReportingPeriod.fldReportingPeriodCode, tblElement.fldElementID, tblPlannedWork.fldProgrammeYear, tblPlannedWork.fldContractorStatusID, tblPlannedWork.fldSEHStatusID " & _
" HAVING (((tblPlannedWork.fldContractorStatusID)=8) AND ((tblPlannedWork.fldSEHStatusID)=11) AND ((tblPlannedWork.fldPropertyRef) " & xxx & " 'bc*')) OR (((tblPlannedWork.fldContractorStatusID)=8) AND ((tblPlannedWork.fldSEHStatusID)=11) AND ((tblPlannedWork.fldPropertyRef) " & xxx & " 'bc*')) " & _
" ORDER BY tblPlannedWork.fldContractorCode, tlkpYear.fldFinancialYear, tblReportingPeriod.fldMonth, tblReportingPeriod.fldYear, tblReportingPeriod.fldMonth;"

Debug.Print sql

Me.RecordSource = sql
Me.Requery
'DoCmd.OpenQuery stDocName, acNormal, acEdit

End Sub

 

[healey21]

look at this reply to a thread, it may help

http://www.mrexcel.com/forum/showthread.php?t=286382