Excel的VBA到MS Access - 查询没有得到执行
问题描述:
下面的代码给出了一个错误和“否为一个或多个参数的给定值”停止在行:rs.open QRY,CNExcel的VBA到MS Access - 查询没有得到执行
然而,如果其他QRY当前被注释掉(SELECT * FROM CallData)被执行,然后代码运行没有任何问题。
我需要做些什么修改才能让代码正确运行GROUP BY查询?
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
答
我会写这样的查询:
SELECT CallData.Bank, Count(CallData.Bank) AS CountOfBank
FROM CallData
GROUP BY CallData.Bank, CallData.TicketDate
HAVING (((CallData.TicketDate)=Date()));
访问时认为一个参数是必须的,它实际上意味着它不能识别的关键字。如果Date是一个函数,你可能需要使用它:'Date()'(按照Jens的答案) – 2015-02-23 11:03:02