在 Access VBA 中运行参数查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10783397/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Running a parameter query in Access VBA
提问by 147
I am trying to run the following query in a VBA function. I keep getting "Too few parameters. Expected 1."
我正在尝试在 VBA 函数中运行以下查询。我不断收到“参数太少。预期为 1”。
strSQL = "Parameters [Report Date] DateTime;" & vbCrLf & _
"SELECT SCF.code AS [Stock Code], " & vbCrLf & _
"SCF.desc AS [Description], " & vbCrLf & _
"SCF.grp AS [Product Group]," & vbCrLf & _
"SCF.qCurr AS [Closing Stock], " & vbCrLf & _
"SCF.abp AS [Avg Price], " & vbCrLf & _
"Sum(([Closing Stock]*[Avg Price])) AS [STOCK VALUE], " & vbCrLf & _
"MaxDate.tDate AS [Last Transaction Date], " & vbCrLf & _
"Sum(IIf(([Last Transaction Date]>[Report Date]),([Closing Stock]*[Avg Price]),0)) AS [After Report Date], " & vbCrLf & _
"DateDiff(""d"",[Last Transaction Date],[Report Date]) AS [Days since Last Transaction], " & vbCrLf & _
"[Report Date]" & vbCrLf & _
"INTO [FinReport] " & vbCrLf & _
"FROM SCF RIGHT JOIN MaxDate ON MaxDate.parent = SCF.this "
strSQL = strSQL & _
"WHERE (SCF.qCurr <> 0) " & vbCrLf & _
"GROUP BY SCF.code, " & vbCrLf & _
"SCF.desc, " & vbCrLf & _
"SCF.grp, " & vbCrLf & _
"SCF.qCurr, " & vbCrLf & _
"SCF.abp, " & vbCrLf & _
"MaxDate.tDate" & vbCrLf & _
"ORDER BY MaxDate.tDate;"
Set qdf = db.CreateQueryDef("", strSQL)
qdf.Parameters("[Report Date]").Value = Form_IO_Form.ReportDate_TB.Value
qdf.Execute
I have verified that all fields (other than [Report Date] of course) exist and the query runs by itself as an access query(pop up asks for [Report Date]).
我已验证所有字段（当然 [Report Date] 除外）都存在，并且该查询作为访问查询自行运行（弹出询问 [Report Date]）。
Help!
帮助！
Edit 1:
As requested here is the DB file as a ZIP. It is an Access 2007 .accdb file
DB File
编辑 1：根据这里的要求是 ZIP 格式的 DB 文件。它是一个 Access 2007 .accdb 文件
DB 文件
采纳答案by HansUp
Your SQL statement will trigger error #3122 from the db engine:
您的 SQL 语句将从数据库引擎触发错误 #3122：
You tried to execute a query that does not include the specified expression 'DateDiff("d",[Last Transaction Date],[Report Date])' as part of an aggregate function.
You tried to execute a query that does not include the specified expression 'DateDiff("d",[Last Transaction Date],[Report Date])' as part of an aggregate function.
That error will cause the statement to fail before the db engine even considers any parameters.
该错误将导致语句在数据库引擎甚至考虑任何参数之前失败。
When you build a SQL statement with VBA, it's better to start with one the db engine will accept. Then you should also follow the sound advice from @mwolfe02 to Debug.Print strSQL... to give yourself an opportunity to examine the completed statement you're asking the db engine to execute.
当您使用 VBA 构建 SQL 语句时，最好从数据库引擎接受的语句开始。然后，您还应该遵循来自@mwolfe02 的合理建议Debug.Print strSQL...，让自己有机会检查您要求数据库引擎执行的完整语句。
Edit: Having examined the ACCDB file you uploaded, I still don't understand why your query doesn't trigger error #3122. However the query does work as a saved query and can work when you execute it from VBA code. The reason you got the complaint about "too few parameters" is that you weren't actually executing the temporary QueryDef you created. Instead you were attempting to execute the SQL text like this:
编辑：检查了您上传的 ACCDB 文件后，我仍然不明白为什么您的查询不会触发错误 #3122。但是，该查询确实可以作为保存的查询使用，并且可以在您从 VBA 代码中执行时使用。您抱怨“参数太少”的原因是您实际上没有执行您创建的临时 QueryDef。相反，您试图像这样执行 SQL 文本：
' Execute created Query '
CurrentDb.Execute strSQL, dbFailOnError
If you change to this approach (as you indicated in your question), it works without error:
如果您更改为这种方法（如您在问题中所指出的），它可以正常工作：
qdf.Execute
回答by mwolfe02
I am guessing that you have a typo in one of your field names. The easiest way to find it is to throw a Debug.Print strSQLline immediately before your Set qdf...line.
我猜你的一个字段名称有错别字。找到它的最简单方法是Debug.Print strSQL在您的Set qdf...行之前立即抛出一行。
Then create a new query in the Access UI, switch to SQL view, paste in the SQL text from the immediate window, and execute the query. Access will prompt you for the Report Date(which you are expecting) and the mistyped name of one of your fields.
然后在 Access UI 中创建一个新查询，切换到 SQL 视图，从即时窗口粘贴 SQL 文本，然后执行查询。Access 将提示您输入Report Date（您期望的）和您的字段之一的错误输入名称。
