Thread: Problem with Aggregate Function ADO SQL

Hi,

I have a spreadsheet that I populate from an Access database which I have no problem doing, except for the below query.

The SQL query works fine in Access, but returns no records when running the below code from Excel and there are no error messages. This is the first Aggregate function that I have run from Excel so it's quite possible I'm missing something, but any help would be very much appreciated!

Code:

Option Explicit

Public Sub ImportSD()


Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sql As String
Dim filenm As String
Dim scrh As String
Dim x As Double, y As Double
Dim usernm As String, pword As String
Dim lSQL As String

filenm = "\\Sheffield-1\data\bus\339-BusContactCentreSales\Private\WIP Reports\Payplan.mdb"

Set conn = New ADODB.Connection
conn.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & filenm & ";", _
              usernm, pword
              
lSQL = "SELECT dateadd('m', datediff('m', 0,[entry Date]),0) As [Set_Month]," & _
       " SUM([Points]) AS [Run_Rate] " & _
       "FROM Combined WHERE [Product Name] LIKE '*BROADBAND*' " & _
       "GROUP BY dateadd('m', datediff('m', 0,[entry Date]),0);"
       

Set rs = New ADODB.Recordset
rs.Open lSQL, conn, adOpenStatic

Sheets("teams").Range("A38").CopyFromRecordset rs


rs.close
Set rs = Nothing
conn.Close
Set conn = Nothing

End Sub

Hi

I believe it's because in this ADO provider you need to use the % character to denote a wildcard so the SQL should look like this:

Code:

lSQL = "SELECT dateadd('m', datediff('m', 0,[entry Date]),0) As [Set_Month]," & _
       " SUM([Points]) AS [Run_Rate] " & _
       "FROM Combined WHERE [Product Name] LIKE '%BROADBAND%' " & _
       "GROUP BY dateadd('m', datediff('m', 0,[entry Date]),0);"

Richard