VBA无法识别查询的未绑定文本框的值

Question

我在Form中有两个未绑定的文本框，用户在其中设置查询的开始和结束日期。然后用户点击按钮生成报告。

一切正常，除了Access弹出一个对话框，询问开始和停止日期，即使变量myStartDate和myEndDate具有正确的值。

我怀疑我错过了一些简单的东西。

Private Sub PrintReport_Click()
    Dim myForm As Form
    Dim myTextBox As TextBox
    Dim myStartDate As Date, myEndDate As Date
    myStartDate = CDate(Forms![Data Entry - Ammonia and Alkalinity]![StartDate])
   myEndDate = CDate(Forms![Data Entry - Ammonia and Alkalinity]![EndDate])
   Dim whereString As String
   whereString = "LabDate Between myStartDate And myEndDate"
   DoCmd.OpenReport "Ammonia and Alkalinity Report", acViewPreview, , whereString
End Sub

Answer 1

如果您（我想是这样）已经将日期格式应用于两个文本框，则不需要进行大部分转换，但必须将日期值的格式化字符串表达式传递给SQL代码：

Private Sub PrintReport_Click()
    Dim myForm As Form
    Dim myTextBox As TextBox
    Dim myStartDate As String
    Dim myEndDate As String
    Dim whereString As String
    myStartDate = Format(Forms![Data Entry - Ammonia and Alkalinity]![StartDate], "yyyy/mm/dd")
    myEndDate = Format(Forms![Data Entry - Ammonia and Alkalinity]![EndDate], "yyyy/mm/dd")
    whereString = "LabDate Between #" & myStartDate & "# And #" & myEndDate & "#"
    DoCmd.OpenReport "Ammonia and Alkalinity Report", acViewPreview, , whereString
End Sub

Answer 2

您需要转义字符串才能使用这些变量。

你想要的是：

whereString = "LabDate Between #" & myStartDate & "# AND #" & myEndDate & "#"

Answer 3

修复字符串为的位置：-

whereString = "LabDate Between " & _
"#" & Format (myStartDate, "mm/dd/yyyy")  & " #" & _
 " And " & _
"#" & Format (myEndDate, "mm/dd/yyyy") & "#"