用于向表单添加日期范围以改进搜索的VBA代码

Question

我已经有一个带有"Type“(这是我正在搜索的抗体字段记录)的Combo下拉列表的表单。"SearchRecords“按钮启动代码。我想在表单中添加开始和结束日期，以优化搜索。我已经为StartDate和EndDate创建了日期文本框。我当前的代码如下所示。我需要VB代码的帮助来获得日期范围。

Private Sub SearchRecords_Click()
    Dim strSQL As String
    On Error Resume Next
    Combo24.SetFocus
    DoCmd.Hourglass True
    strSQL = "SELECT * FROM tblAGV_Log WHERE [Type] = '" & Me.Combo24.Text & "' Order by Date DESC"
    Me.RecordSource = ""
    Me.RecordSource = strSQL
    DoCmd.Hourglass False
End Sub

Answer 1

展示它比描述它容易，但我会像这样扩展你的例程：

Private Sub SearchRecords_Click()
    Dim strSQL As String
    Dim strWhere As String
    Dim strOp As String

    On Error Resume Next

    DoCmd.Hourglass True
    strSQL = "SELECT * FROM tblAGV_Log"

    strWhere = ""
    strOp = ""
    If Not IsNull(Me.Combo24.Value) Then
        strWhere = strWhere & strOp & "([Type] = '" & Me.Combo24.Value & "')"
        strOp = " And "
    End If
    If Not IsNull(Me.StartDate.Value) Then
        strWhere = strWhere & strOp & "([Date] >= #" & Me.StartDate.Value & "#)"
        strOp = " And "
    End If
    If Not IsNull(Me.EndDate.Value) Then
        strWhere = strWhere & strOp & "([Date] <= #" & Me.EndDate.Value & "#)"
        strOp = " And "
    End If

    If Len(strWhere) > 0 then
        strSQL = strSQL & " WHERE " & strWhere
    End If
    strSQL = strSQL & " Order by [Date] DESC"

    Me.RecordSource = strSQL
    DoCmd.Hourglass False

End Sub

这将使您非常接近。