[X]My Assistant

[didacticone]

I have a form and i would like to filter by two criteria- employee and/or date range. My form has three unbound text boxes: empsearch, begindate, enddate. I would like to be able to filter the records by three conditions:

1)Enter only the dates in begindate and enddate, and leave empsearch empty
2)Enter only the employee in empsearch and leave begindate and enddate empty
3)Enter the dates and the employee

The code I am currently using only allows for option 3... can anyone help me out? Thanks.

I am using the current code:

CODE

Private Sub Command22_Click()
    On Error GoTo EH
    Dim fSearch As Boolean

    fSearch = True

    If Nz(Me.begindate) = vbNullString Or Nz(Me.enddate) = vbNullString Then
        MsgBox "You must enter both dates"
        fSearch = False
    Else
        If Nz(Me.begindate) = vbNullString And Nz(Me.enddate) = vbNullString Then
            If Nz(Me.empsearch) = vbNullString Then
                MsgBox "You must enter search criteria"
                fSearch = False
            End If
        End If
    End If

    If fSearch Then
        Me.Filter = IIf(Nz(Me.begindate) = vbNullString, "", _
            "[date1] BETWEEN #" & Me.begindate & "# AND #" & Me.enddate & "#") & _
            IIf(Nz(Me.begindate) = vbNullString, "", " AND ") & _
            IIf(Nz(Me.empsearch) = vbNullString, "", "[employee] Like '" & Me.empsearch & "*'")
    End If

    Exit Sub
EH:
    MsgBox "There was an error with the search!  " & _
        "Please contact your Database Administrator.", vbCritical, "WARNING!"
    Exit Sub

End Sub

[theDBguy]

Hi,

Welcome to UtterAccess!


I do it this way...

CODE

Dim strFilter As String

strFilter = " 1 = 1 "

If Me.empsearch > "" Then
    strFilter = strFilter & " AND [employee] Like '*" & Me.empsearch & "*' "
End If

If Me.begindate > "" Then
    strFilter = strFilter & " AND [date1]>=#" & Me.begindate & "# "
End If

If Me.enddate > "" Then
    strFilter = strFilter & " AND [date1]<=#" & Me.enddate & "# "
End If

Hope it helps...

[BruceM]

I will assume the controls for date range and employee are unbound.

You can do something like this:

CODE

Dim strFilter As String

If Not IsNull(Me.BeginDate) Or IsNull(Me.EndDate) Then
    strFilter = " AND Date1 Between " & Format(Me.BeginDate,"\#yyyy\/mm\/dd\#") & " And " & Format(Me.EndDate,"\#yyyy\/mm\/dd\#")
End If

If Not IsNull(Me.EmpSearch) Then
    strFilter = strFilter & " AND Employee = """ & Me.EmpSearch & """"
End If

If Len(strFilter) > 0 Then
    strFilter = Mid(strFilter,5)   ' Remove the leading " AND "
    Debug.Print strFilter
    Me.Filter = strFilter
    Me.FilterOn = True
End If

See this link for more information about Debug.Print and other debugging techniques.
This link includes quite a number of links about design and so forth, and may have something you will find useful.

I urge you to reconsider using an employee name, if that's what you are doing, rather than assigning a numeric identifier (autonumber PK is a good choice) to the employee record. I used """ and """" rather than '" and "'" because names often include an apostrophe, which will cause an error in a string delimited by single quotes (which are just apostrophes by another name).

I formatted the dates as shown because I don't know what regional format you have. Access expects mm/dd/yyyy (or m/d/yy, etc.) or yyyy/mm/dd. Some dates in dd/mm/yyyy format will be understood by Access. For instance, 6/3/2018 will be understood as June 3 where March 6 was intended. It may not be necessary, depending on the actual values in the text boxes. Debug.Print will let you evaluate the filter string to be sure it is what you want.

Edit: DBGuy has something quite similar to what I suggested. I like the trick of starting strFilter with 1 = 1, which eliminates the need to use Mid to remove the leading " AND ". Also, he added asterisks to work with the Like comparison, which I meant to do (or else to mention that for a single name you may want = rather than Like).

[projecttoday]

I would add some validation prior to building strFilter.

I prefer Bruce's way because do you really want WHERE 1 = 1 when nothing at all was entered?

[BruceM]

Actually, I will keep doing it the way I described, but I like the logic behind the other technique. I think, but am not certain, that having True as a filter would be the same as not having a filter, in case no date or employee criteria are entered.

I'm curious as to what you mean about adding validation. I expect I will recognize it when you describe it, but right now I don't get it.

[projecttoday]

CODE

If not Isnull(Me.Begindate) then
if not Isdate(Me.begaindate) then
msgbox "Invalid entry for begindate"
Exit Sub
End If
End If

If not Isnull(Me.Enddate) then
if not Isdate(Me.Enddate) then
msgbox "Invalid entry for Enddate"
Exit Sub
End If
End If

If (Isnull (Me.Begindate) and Not Isull(Me.Enddate)) or (Isnull(Me.Enddate) and Not Isnull(Me.Enddate)) Then
msgbox "Must enter both begin and end dates"
Exit Sub
End If

[BruceM]

Oh, I see what you mean. Thanks!

[didacticone]

Thanks for all the help! Sorry we had a snowstorm and I haven't been able to get back to you. Here is what I was able to come up with based on some suggestions:

CODE

On Error GoTo EH
    Dim fSearch     As Boolean
    Dim strFilter   As String

    fSearch = True

    If Nz(Me.empsearch) = vbNullString Then
        If Nz(Me.begindate) = vbNullString _
            And Nz(Me.enddate) = vbNullString Then
            MsgBox "You must enter search criteria"
            fSearch = False
        Else
            If Nz(Me.begindate) = vbNullString _
                Or Nz(Me.enddate) = vbNullString Then
                MsgBox "You must enter both dates"
                fSearch = False
            End If
        End If
    Else
        If Not (Nz(Me.begindate) = vbNullString _
            And Nz(Me.enddate) = vbNullString) Then
            If Nz(Me.begindate) = vbNullString _
                Or Nz(Me.enddate) = vbNullString Then
                MsgBox "You must enter both dates"
                fSearch = False
            End If
        End If
    End If

    If fSearch Then
        strFilter = IIf(Nz(Me.begindate) = vbNullString, "", _
            "[date1] BETWEEN #" & Me.begindate & _
                "# AND #" & Me.enddate & "#") & _
            IIf(Nz(Me.empsearch) = vbNullString, "", _
                IIf(Nz(Me.begindate) = vbNullString, "", " AND ") & _
                "[employee] Like '*" & Me.empsearch & "*'")
        Debug.Print strFilter
        Me.Filter = strFilter
        Me.FilterOn = True
    End If

    Exit Sub
EH:
    MsgBox "There was an error with the search!  " & vbCrLf & vbCrLf & _
        Err.Description & vbCrLf & vbCrLf & _
        "Please contact your Database Administrator.", vbCritical, "WARNING!"
    Exit Sub

[theDBguy]

Hi,

Glad to hear you got it sorted out. Good luck with your project.