Access VBA执行日期区间查询弹出输入参数值提示如何解决

问题根因 #

你遇到的「Enter Parameter Value」弹窗是两个语法错误导致的：

• 表名不匹配：FROM子句引用的表名是dbo_vw_busobj_file_rejections_load_access_temp5_copy，但WHERE、ORDER BY子句中引用表字段时，漏写了表名前缀的vw标识，写成了dbo_busobj_file_rejections_load_access_temp5_copy，Access无法识别不存在的表字段，就会判定为需要手动输入的参数。
• SELECT子句末尾冗余逗号：字段AS [Date]后多了一个多余的逗号，也会导致SQL语法解析异常。

解决方法 #

直接修正SQL拼接逻辑的语法错误即可，修正后的代码如下：

Dim SQLAllReject As String
Dim strDateFrom As String
Dim strDateTo As String

strDateFrom = Format(txtDate.Value, "mm/dd/yyyy")
strDateTo = Format(txtDateTo.Value, "mm/dd/yyyy")

SQLAllReject = "SELECT dbo_vw_busobj_file_rejections_load_access_temp5_copy.HashKey AS [ID], dbo_vw_busobj_file_rejections_load_access_temp5_copy.Reject_Date AS [Date] " & _
    "FROM dbo_vw_busobj_file_rejections_load_access_temp5_copy " & _
    "WHERE (((dbo_vw_busobj_file_rejections_load_access_temp5_copy.Reject_Date) Between #" & strDateFrom & "# And #" & strDateTo & "#)) " & _
    "ORDER BY dbo_vw_busobj_file_rejections_load_access_temp5_copy.Reject_Date DESC;"

' 调试用：可以把生成的SQL打印到立即窗口，复制到查询设计器运行即可直接定位报错
Debug.Print SQLAllReject

如果后续要优化避免类似字符串拼接问题，也可以改用Access参数查询的写法，不用拼接日期字符串，也能规避格式、注入风险：

Dim qdf As QueryDef
Set qdf = CurrentDb.CreateQueryDef("", _
    "PARAMETERS [StartDate] DateTime, [EndDate] DateTime; " & _
    "SELECT HashKey AS [ID], Reject_Date AS [Date] " & _
    "FROM dbo_vw_busobj_file_rejections_load_access_temp5_copy " & _
    "WHERE Reject_Date Between [StartDate] And [EndDate] " & _
    "ORDER BY Reject_Date DESC;")
qdf!StartDate = txtDate.Value
qdf!EndDate = txtDateTo.Value
' 后续可以用qdf.OpenRecordset获取查询结果

内容的提问来源于stack exchange，提问作者Grant Doole