This is the code I am working with...
â~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
âStart of Code
â~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Private Sub Report_Open(Cancel As Integer)
Dim cnnReport As New ADODB.Connection
Dim rstReport As New ADODB.Recordset
With cnnReport
.Provider = strProvider
.Properties("Data Source") = strDataSource
.Properties("Jet OLEDB:Database Password") = strPropDatabasePassword
.Open
End With
strSQL = "SELECT FormatDateTime([dtmExport],2) AS ExportDte,"
strSQL = strSQL & "DateDiff('d', tbl002TaskTracking.dtmExport ,#" & Date & "#) AS DaysOut,"
strSQL = strSQL & "Sum(IIf([bytTaskId]=1,1,0)) AS [Total],"
strSQL = strSQL & "Sum(IIf([strTaskGroup]='ICO-ST' And [bytTaskId]=1,1,0)) AS [Total ST],"
strSQL = strSQL & "Sum(IIf([strTaskGroup]='ICO-EX' And [bytTaskId]=1,1,0)) AS [Total EX],"
â~~~~~~~~~~~~~~~~
âWhy are there additional parenthesis around the sum function you may ask.
â I have NO idea. At this point I am copying the SQL directly from the query window in Access
â~~~~~~~~~~~~~~~~
strSQL = strSQL & "(Sum(IIf([bytTaskId]=1 And [strtaskstatus]<>'Complete',1,0))) AS [Phase1],"
strSQL = strSQL & "(Sum(IIf([bytTaskId]=2 And [strtaskstatus]<>'Complete',1,0))) AS [Phase2],"
strSQL = strSQL & "(Sum(IIf([bytTaskId]=3 And [strtaskstatus]<>'Complete',1,0))) AS [Phase3],"
strSQL = strSQL & "(Sum(IIf([bytTaskId]=4 And [strtaskstatus]<>'Complete',1,0))) AS [Phase4],"
strSQL = strSQL & "(Sum(IIf([bytTaskId]=5 And [strtaskstatus]<>'Complete',1,0))) AS [Phase5],"
strSQL = strSQL & "(Sum(IIf([bytTaskId]=6 And [strtaskstatus]<>'Complete',1,0))) AS [Phase6],"
strSQL = strSQL & "(Sum(IIf([bytTaskId]=7 And [strtaskstatus]<>'Complete',1,0))) AS [Phase7],"
strSQL = strSQL & "(Sum(IIf([bytTaskId]=8 And [strtaskstatus]<>'Complete',1,0))) AS [Phase8],"
strSQL = strSQL & "(Sum(IIf([bytTaskId]=9 And [strtaskstatus]<>'Complete',1,0))) AS [Phase9],"
strSQL = strSQL & "(Sum(IIf([bytTaskId]=10 And [strtaskstatus]<>'Complete',1,0))) AS [Phase10],"
strSQL = strSQL & "(Sum(IIf([bytTaskId]<>11 And [strtaskstatus]<>'Complete',1,0))) AS [Phase11] "
strSQL = strSQL & "FROM tbl002TaskTracking "
strSQL = strSQL & "GROUP BY FormatDateTime([dtmExport],2), "
strSQL = strSQL & "DateDiff('d',[dtmExport],#" & Date & "#) "
â~~~~~~~~~~~~~~~~~
âAgain below Access seems to LOVE parenthesis
â~~~~~~~~~~~~~~~~~
strSQL = strSQL & "HAVING ((((Sum(IIf([bytTaskId]<>11 And [strtaskstatus]<>'Complete',1,0))))<>0)) "
strSQL = Replace(strSQL, "'", "''")
With rstReport
Set .ActiveConnection = cnnReport
.CursorType = adOpenStatic
.CursorLocation = adUseClient
.LockType = adLockReadOnly
.Open strSQL
End With
End Sub
â~~~~~~~~~~~~~~~~~~~~
âEnd of Code
â~~~~~~~~~~~~~~~~~~~~
I've also tried...
strSQL = "TRANSFORM Count(tbl001Detail.lngID) AS CountOflngLoanID "
strSQL = strSQL & "SELECT Format([tbl001Detail].[dtmExport],"yyyy-mm-dd") AS [Date] "
strSQL = strSQL & "FROM tbl001Detail "
strSQL = strSQL & "INNER JOIN tbl002TaskTracking "
strSQL = strSQL & "ON tbl001Detail.lngID = tbl002TaskTracking.lngID "
strSQL = strSQL & "WHERE tbl002TaskTracking.strTaskStatus <> 'Complete' "
strSQL = strSQL & "AND tbl002TaskTracking.strTaskStatus <> 'cawd' "
strSQL = strSQL & "GROUP BY Format([tbl001Detail].[dtmExport],'yyyy-mm-dd') "
strSQL = strSQL & "PIVOT tbl002TaskTracking.strTaskName
strSQL = strSQL & "IN ('Phase1',âPhase2â,âPhase3â,âPhase4â,â Phase5','Phase6','Phase7','Phase8','Phase9')
strSQL = Replace(strSQL, "'", "''")
I always seem to be getting my error in ANY aggregate functions within the select statement, however Iâm not sure how to get around it since this is a summary. All functions involving my date seem to be going awry.
|