UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Filtering Multiple Criteria With And Witthout Null, Access 2010    
 
   
didacticone
post Mar 16 2018, 08:38 AM
Post#1



Posts: 10
Joined: 16-March 18



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
Go to the top of the page
 
theDBguy
post Mar 16 2018, 09:49 AM
Post#2


UA Moderator
Posts: 76,906
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Welcome to UtterAccess!
welcome2UA.gif

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...
Go to the top of the page
 
BruceM
post Mar 16 2018, 09:59 AM
Post#3


UtterAccess VIP
Posts: 7,993
Joined: 24-May 10
From: Downeast Maine


welcome2UA.gif
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).
Go to the top of the page
 
projecttoday
post Mar 16 2018, 10:22 AM
Post#4


UtterAccess VIP
Posts: 11,300
Joined: 10-February 04
From: South Charleston, WV


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?
Go to the top of the page
 
BruceM
post Mar 16 2018, 10:32 AM
Post#5


UtterAccess VIP
Posts: 7,993
Joined: 24-May 10
From: Downeast Maine


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.
Go to the top of the page
 
projecttoday
post Mar 16 2018, 10:57 AM
Post#6


UtterAccess VIP
Posts: 11,300
Joined: 10-February 04
From: South Charleston, WV


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
Go to the top of the page
 
BruceM
post Mar 16 2018, 11:00 AM
Post#7


UtterAccess VIP
Posts: 7,993
Joined: 24-May 10
From: Downeast Maine


Oh, I see what you mean. Thanks!
Go to the top of the page
 
didacticone
post Mar 22 2018, 06:49 AM
Post#8



Posts: 10
Joined: 16-March 18



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
Go to the top of the page
 
theDBguy
post Mar 22 2018, 09:33 AM
Post#9


UA Moderator
Posts: 76,906
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Glad to hear you got it sorted out. Good luck with your project.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    14th December 2019 - 09:51 PM