Excel VBA to MS Access - Query not getting executed

Question

The code below gives an error "No value given for one or more parameters" and stops at the line : rs.open qry, cn

However if the other qry which is currently commented out (SELECT * FROM CallData) is executed, then the code runs without any problem.

What modifications do I need to make for the code to run correctly with the GROUP BY Query?



    Dim cn As Object
    Dim rs As Object
    Dim qry As String
    Dim Insertws As Worksheet
    Dim strConnectString As String
    Set Insertws = ThisWorkbook.Sheets("temp")
    Insertws.Cells.Clear
   strConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\corpfiler09\26663700_N01\NewAstt\ASTT.MDB;Jet OLEDB:Database Password=test;"
    'Connect Database; insert a new table
    Set cn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    'qry = "SELECT * FROM CallData;"
    qry = "SELECT Bank, Count(Bank) from CallData where TicketDate = Date  GROUP BY Bank"

cn.Open strConnectString
rs.Open qry, cn

When access thinks a parameter is required it actually means it doesn't recognise a keyword. If Date is a function you probably need to use this instead: Date() (as per Jens answer) — Nick.McDermaid

Jens Jens · Accepted Answer · 2015-02-23T10:41:48

I'd write the query like this:

SELECT CallData.Bank, Count(CallData.Bank) AS CountOfBank
FROM CallData
GROUP BY CallData.Bank, CallData.TicketDate
HAVING (((CallData.TicketDate)=Date()));

Excel VBA to MS Access - Query not getting executed

1 Answers