Gregorys05
Board Regular
- Joined
- Sep 24, 2008
- Messages
- 217
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
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