访问输入框重复用户提示
我有一个很大的 Access 表。我有一个名为 qry_ExtractYear 的查询，我希望用户修改条件并导出结果。我这里的代码可以工作，但我遇到了在导出之前重复提示 sDate1 和 sDate2 的问题。请问我做错了什么？
Sub ExportAnnualQuoteActivity()
Dim db As dao.Database
Set db = CurrentDb
Dim qdf As dao.QueryDef
Dim sDate1 As String
Dim sDate2 As String
sDate1 = InputBox(prompt:="Start Date YYYYMMDD")
sDate2 = InputBox(prompt:="End Date YYYYMMDD")
Set qdf = db.QueryDefs("Qry_ExtractYear")
qdf.SQL = "Select * From [tbl_QuoteData] WHERE [Quote Date] BETWEEN sDate1 AND sDate2"
DoCmd.Save acQuery, "Qry_ExtractYear"
DoCmd.OutputTo acOutputQuery, "Qry_ExtractYear", acFormatXLSX, "T:\Actuary\Metrics\NB\Data\Yearly\tbl_QuoteData_" & Left(sDate2, 4) & ".xlsx", True
End Sub
I have a large Access table. I have a query entitled qry_ExtractYear which I would like to have the criteria modified by the user and the the results exported. I have the code here which works but I am having the issue of being prompted repeatedly for sDate1 and sDate2 before the export occurs. What am I doing wrong please?
Sub ExportAnnualQuoteActivity()
Dim db As dao.Database
Set db = CurrentDb
Dim qdf As dao.QueryDef
Dim sDate1 As String
Dim sDate2 As String
sDate1 = InputBox(prompt:="Start Date YYYYMMDD")
sDate2 = InputBox(prompt:="End Date YYYYMMDD")
Set qdf = db.QueryDefs("Qry_ExtractYear")
qdf.SQL = "Select * From [tbl_QuoteData] WHERE [Quote Date] BETWEEN sDate1 AND sDate2"
DoCmd.Save acQuery, "Qry_ExtractYear"
DoCmd.OutputTo acOutputQuery, "Qry_ExtractYear", acFormatXLSX, "T:\Actuary\Metrics\NB\Data\Yearly\tbl_QuoteData_" & Left(sDate2, 4) & ".xlsx", True
End Sub
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
这是因为构造的 SQL 具有文字文本“sDate1 AND sDate2”，而不是这些变量的值。连接变量并使用日期/时间#分隔符。
如果字段是文本类型，请使用撇号分隔符而不是#。数字字段不需要分隔符。
建议不要使用 InputBox，因为无法轻松验证用户输入。可变用户输入的表单上的参考控件。
It's because the constructed SQL has literal text "sDate1 AND sDate2" and not the values of those variables. Concatenate variables and use date/time # delimiter.
If field is text type, use apostrophe delimiter instead of #. Number fields do not require delimiters.
Advise not to use InputBox as cannot easily validate user input. Reference controls on form for variable user input.