Convert VBA Filter to SQL Where Clause

[speakers_86]

I have a rather complex where clause that is built dynamically based on optional user input. I would like to convert this from VBA to a standard query for increased speed. I've heard access does some behind the scenes stuff that make regular queries run faster than anything in VBA. I want to take advantage of that. I have also read that what I am doing in code causes the entire table to be transferred over the network and processed at the client's terminal; and indeed, it appears to be slower at remote workstations than at the computer that stores the backend.

The below isn't really that complicated. I guess the toughest part is the way in which I am handling dates. If only start date and time are entered, then everything is included from that date time going forward. If only end date and time is entered, then anything older than that date is included. If both start and end are entered, then anything between.

Of course, all of these fields are optional, so that is another complication...

Any tips on how to convert this? Is it even worth the effort?

Public Sub cmdFilter_Click()
    On Error GoTo err

    Dim strWhere As String
    Dim lngLen As Long
    Dim dteStart As Date
    Dim dteEnd As Date

    If Not IsNull(Me.txtSubject) Then
        strWhere = strWhere & "([tblNotes].[Subject] Like ""*" & Me.txtSubject & "*"") AND "
    End If

    If Not IsNull(Me.txtBody) Then
        strWhere = strWhere & "([Note] Like ""*" & Me.txtBody & "*"") AND "
    End If

    If Not IsNull(Me.cboMissionID) Then
        strWhere = strWhere & "([MissionID] = " & Me.cboMissionID & ") AND "
    End If

    If Not IsNull(Me.cboTerminalID) Then
        strWhere = strWhere & "([Terminal] Like ""*" & Me.cboTerminalID.Column(1) & "*"") AND "
    End If

    If Not IsNull(Me.cboUserID) Then
        strWhere = strWhere & "([UserID] = " & Me.cboUserID & ") AND "
    End If

    If Not IsNull(Me.cboRemedyTicketID) Then
        strWhere = strWhere & "([SiteIssueID] = " & Me.cboRemedyTicketID & ") AND "
    End If

    If Not IsNull(Me.txtDateStart) And Len(Me.txtDateStart) > 0 Then
        If IsNull(Me.txtTimeStart) Then Me.txtTimeStart = "0000"
        dteStart = Me.txtDateStart + CivilianTime(Nz(Me.txtTimeStart, "0"))
    End If

    If Not IsNull(Me.txtDateEnd) And Len(Me.txtDateEnd) > 0 Then
        If IsNull(Me.txtTimeEnd) Then Me.txtTimeEnd = "2359"
        dteEnd = Me.txtDateEnd + CivilianTime(Nz(Me.txtTimeEnd, "2359"))
    End If

    If Not IsNull(Me.txtTimeStart) And Len(Me.txtTimeStart) > 0 And IsNull(Me.txtDateStart) Or Len(Me.txtDateStart) = 0 Then
        Me.txtDateStart = Date
        dteStart = Me.txtDateStart + CivilianTime(Nz(Me.txtTimeStart, "0"))
    End If
    If Not IsNull(Me.txtTimeEnd) And Len(Me.txtTimeEnd) > 0 And IsNull(Me.txtDateEnd) Or Len(Me.txtDateEnd) = 0 Then
        Me.txtDateEnd = Date
        dteEnd = Me.txtDateEnd + CivilianTime(Nz(Me.txtTimeEnd, "2359"))
    End If

    If Not IsNull(Me.txtDateEnd) And Len(Me.txtDateEnd) > 0 And Not IsNull(Me.txtDateStart) And Len(Me.txtDateStart) > 0 Then
        strWhere = strWhere & "([DateTime] Between #" & dteStart & "# AND #" & dteEnd & "#) AND "
    End If

    If Not IsNull(Me.txtDateEnd) And Len(Me.txtDateEnd) > 0 And IsNull(Me.txtDateStart) Or Len(Me.txtDateStart) = 0 Then
        strWhere = strWhere & "([DateTime] <= #" & dteEnd & "#) AND "
    End If

    If Not IsNull(Me.txtDateStart) And Len(Me.txtDateStart) > 0 And IsNull(Me.txtDateEnd) Or Len(Me.txtDateEnd) = 0 Then
        strWhere = strWhere & "([DateTime] >= #" & dteStart & "#) AND "
    End If

    'See if the string has more than 5 characters (a trailng " AND ") to remove.
    lngLen = Len(strWhere) - 5
    If lngLen > 0 Then
        strWhere = Left(strWhere, lngLen)
        Me!frmSearchSub.Form.Filter = strWhere
        Me!frmSearchSub.Form.FilterOn = True
    End If
    Exit Sub
err:
    ReportError err.Number, err.Description, "Form_frmSearch | cmdFilter_Click"
End Sub

 

[pbaldy]

I suspect what you're referring to is that saved queries are compiled. While true, I don't know that switching will gain that much vs a query in VBA. That said, it looks like the subform would open with all records, then you'd apply the filter. How about opening the subform with no records, then setting it's source? That's what I'd do, and given your existing code it wouldn't be a hard change. Basically at the end instead of the setting filter you'd have:

Me!frmSearchSub.Form.RecordSource = "SELECT Blah FROM TableName WHERE " & strWhere