I'm trying to re-create some Access queries as ADO recordsets via the Jet Provider.

I was under the impression that I could copy the SQL code from the query itself and use that as my SQL string. Apparantly not.

I continue to get this error message...

"Syntax error (missing operator) in query expression."

whenever I use aggregate functions such as
Datediff() or Sum(IIf(X,1,0))

(I'm trying to avoid TRANSFORM and PIVOT but I may have to use them.)

Anyone have any ideas?

I have no idea why, but when operating in ADO mode I find that Application.Execute works whereas DoCmd.RunSql doesn't. I know they should be the same, but...

Generally speaking your error message comes from the fact that the sql string must be ENTIRELY a string, Copy your formula to the group and maybe extra eyes will spot something you've missed. It's probably just a missing set of "".

All the best

Alan Thomas

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.

Apparantly I can use date functions that do not include text strings...

BAD...
DateDiff('d',date1,date2)
Format(Date1,'yyyy-mm-dd')

OK
FormatDateTime(date1,2)

If anyone knows a way around this limitation or even what the specific limitation is I would appreciate knowing about it