VBA to filter dates and name field

[didacticone]

i have a form where i would like to filter within a date range by employee... for example i would want between 2/1/18 - 2/18/18 all records where the employee field is "Joe Smith"

I have managed to get each filter to work separately but not together.

here are my codes:

If Nz(Me.begindate) = vbNullString Or Nz(Me.enddate) = vbNullString Then
    Debug.Print "The variable is Null or Zero-length"
    MsgBox "Enter Dates"
    Else
 Me.Filter = "[date1] BETWEEN #" & Me.begindate & "# AND #" & Me.enddate & "#"
 
 Me.FilterOn = True
 
 End If

Me.Filter = "[employee] Like '" & Me.empsearch & "*'"
Me.FilterOn = True

Is there a way to combine them so that it will filter both?

Thanks!

[twinnyfo]

You should be able to simply combine the two sets of criteria:

Me.Filter = "[date1] BETWEEN #" & Me.begindate & "# AND #" & Me.EndDate & _
    "# AND [employee] Like '" & Me.empsearch & "*'"
Me.FilterOn = True

Hope this hepps!

[didacticone]

So simple, but yet so difficult... thank you!!!

[didacticone]

sorry but one more thing... is there a way to combine it keeping the if statement? right now if nothing is entered in any of the fields it gives a debug error... i would rather a message box?

[didacticone]

also if i leave employee blank and fill in the dates it returns all values correctly but if i leave the date fields empty but input an employee it gives a debug error

[twinnyfo]

Just expand your If Statement to include checks on all criteria. You can make it as elaborate or as simple as you need, as long as you have a way to validate the data on the Form before you run the Filter.

[didacticone]

I'm trying and keep getting syntax errors... sorry, I dont know all the ins and outs of coding. Could you possibly give me an example and i'll keep trying on my end.

[twinnyfo]

I just used what you already had and included the search text box....

If Nz(Me.begindate) = vbNullString _
    Or Nz(Me.EndDate) = vbNullString _
    Or Nz(Me.empsearch) = vbNullString Then
    MsgBox "Enter Dates and search criteria"
Else
    Me.Filter = "[date1] BETWEEN #" & Me.begindate & "# AND #" & Me.EndDate & _
        "# AND [employee] Like '" & Me.empsearch & "*'"
    Me.FilterOn = True
End If

[didacticone]

I actually tried that exact thing but now it makes all fields required. What I would like to do is filter the employee without needing dates entered or need begin and end date required without needing employee entered. I cant figure it out. Thanks for helping with this.

[twinnyfo]

So, the employee is not required? If it is blank then search only by dates, but if it is entered, then filter by dates and Employee?

Then add an IIf() to your Filter String:

If Nz(Me.begindate) = vbNullString _
    Or Nz(Me.EndDate) = vbNullString Then
    MsgBox "Enter Dates"
Else
    Me.Filter = "[date1] BETWEEN #" & Me.begindate & "# AND #" & Me.EndDate & "#" & _
        IIf(Nz(Me.empsearch) = vbNullString,"", " AND [employee] Like '" & Me.empsearch & "*'")
    Me.FilterOn = True
End If

Hope that hepps!

[didacticone]

OK, so that worked perfect for not needing to enter an employee, just dates, but what if i also wanted to just enter an employee and leave the dates blank, so it returns everything with that employee name?

[twinnyfo]

Use the same principles but in reverse. You can limit your If-Then to determine if there are either dates or employee entered or both, then, based on those facts, determine how to create the filter.

Please use the example I provided to work toward a solution and post what you come up with. We can troubleshoot from there if it does not work.

Keep in mind that this is on the edge of redirecting the original intent of this thread. I will let this go, because I think you have the solution you need and should have the tools to modify that solution for alternate scenarios.

[didacticone]

Understand I am not redirecting anything... im trying to follow your concepts and learn. Unfortunately this doesn't come as easy to me. i have never used "iif" before either. I was researching it to see what it does but i am confused as to how to apply it properly to a between statement. i have modified the code slightly to this:

Private Sub Command22_Click()
 
If Nz(Me.begindate) = vbNullString _
    Or Nz(Me.enddate) = vbNullString _
    Or Nz(Me.empsearch) = vbNullString Then
    MsgBox "Enter Dates"
Else
    Me.Filter = "[date1] BETWEEN #" & Me.begindate & "# AND #" & Me.enddate & "#" & _
        IIf(Nz(Me.empsearch) = vbNullString, "", " AND [employee] Like '" & Me.empsearch & "*'")
    Me.FilterOn = True
    End If
End Sub

but obviously that just requires all fields be entered... its like im thinking backwards... if you dont want to help anymore thats fine but understand i am trying to follow along and learn this... i have used this site on and off for a long time and its been a great learning tool

[twinnyfo]

I take it you want to have two possible conditions: 1) Enter the dates and 2) enter the dates and enter an employee?

If this is the case, just set up those conditions with your If-Then statements. Depending upon the outcome of those conditions, create your Filter String.

Using and IIf() statement is similar to a set of If-Then statements. However, rather than executing code (with the If-Then), you are generating text. So, in the example above, if the empsearch text is empty, then, the empty string ("") is added to your Filter String. If not, it adds the additional search criteria.

I can help your work through your solutions.

[didacticone]

My apologies for not articulating what I looking to do correctly. I would like the following conditions:

1)Enter only the dates
2)Enter only the employee
3)Enter the dates and the employee

You have helped me work through 1 and 3, but I cannot figure out 2.

Does that explain it better? Once again, sorry for not being clear.

[didacticone]

And thanks for the explanation of the IIf statement. I would assume to achieve what I am looking to do this would need to be added to the filter for the date code? but because that is a between statement I cannot seem to figure out the syntax and am getting really confused.

[twinnyfo]

Without writing the entire procedure, I will skeleton outline what you need to do:

Private Sub Command22_Click()
On Error GoTo EH
    Dim fSearch As Boolean
 
    fSearch = True
 
    If 'ONLY one Date is entered' Then
        MsgBox "You must enter both dates"
        fSearch = False
    Else
        If 'BOTH Dates Are EMPTY' Then
            If 'Employee is empty' Then
                MsgBox "You must enter search criteria"
                fSearch = False
            End If
        End If
    End If
 
    If fSearch Then
        'Build Your Filter
    End If
 
    Exit Sub
EH:
    MsgBox "There was an error with the search!  " & _
        "Please contact your Database Administrator.", vbCritical, "WARNING!"
    Exit Sub
End Sub

Notice that I have added a Boolean variable that is set to True at the beginning of the procedure. If anything triggers a bad set of data, it is set to False, which idicates that we cannot procede.

I also want to encourage you in the following way: I am confident that you have the ability to work through this. You have much of what you need already, but just need to make minor modifications to what you have to suit the conditions you are looking for.

As you come across hurdles, I will work through those with you.

[didacticone]

OK, thanks for the info and kind words, i need them as my brain is fried. I have applied the following code:

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 = "[date1] BETWEEN #" & Me.begindate & "# AND #" & Me.enddate & "#" & _
        IIf(Nz(Me.empsearch) = vbNullString, "", " AND [employee] Like '" & Me.empsearch & "*'")
    Me.FilterOn = True
    End If
 
    Exit Sub
EH:
    MsgBox "There was an error with the search!  " & _
        "Please contact your Database Administrator.", vbCritical, "WARNING!"
    Exit Sub
End Sub

It allows me to enter just dates and leave the employee field blank but I still cannot enter an employee and leave the date fields blank. Im guessing this is based on my filter still being incorrect?

[twinnyfo]

Bravo! Your If-Thens look perfect.

To think through your Filter String apply your Conditions:

1)Enter only the dates
2)Enter only the employee
3)Enter the dates and the employee

What would your Filter look like in each of those conditions (think about what they need to llok like independently first)? Then, how would you create code that will duplicate those strings based upon your conditions?

Right now, you have conditions 1 and 3 covered, but not 2. How will you modify this filter? The modifications will be very similar to what we already have.

[didacticone]

My thinking based on what you explained earlier, perhaps I'm wrong though, is we need to add the IIf () to the date part of the filter? I have tried this, but since it is a between statement and referring to multiple fields I am struggling with the syntax.

[didacticone]

I tried something like this:

Me.Filter = "[date1] BETWEEN #" & iif(nz(Me.begindate) = vbnullstring, "", & "# AND #" & iif(nz(Me.enddate) = vbnullstring, "", & "#" & _
        IIf(Nz(Me.empsearch) = vbNullString, "", " AND [employee] Like '" & Me.empsearch & "*'")

Im trying man, lol, just not totally grasping it yet.

[twinnyfo]

So, you're almost there.

Think of it this way: If there are no dates at all (but the code got to this point) then all we are looking for is the Employee. If there are any dates, then we know there are TWO dates (remember our Boolean variable). So, we just need to check one date to see if we filter by dates:

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 & "*'")

You also have to add a middle IIf() statement that will add the "AND" only if your are searching both criteria.

Hope this hepps!

[didacticone]

Now im getting a "compile error: expected list seperator or )" with the following code

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

[twinnyfo]

Line 2:

    "[date1] BETWEEN #" & Me.begindate & "# AND #" & Me.enddate & "#") & _

I don't have the ability to run this code live, mso I forgot the closing parenthesis.

[didacticone]

now it brings up a compile error: expected expression... i was looking through all the parenthesis also

[twinnyfo]

In post #23, your line 3. Simple question. Why keep adding additional conditions when they are not required?

In addition to my correction in Post #24, Line 3 needs a line continuator at the end (an underscore after the "&").

[didacticone]

Simple answer, i was just changing the code around trying different things and didnt realize i posted that version. Like i was saying im trying to get an better feel for what the codes do, so im trying.

I added your corrections and now it backs all three fields required. I cannot enter just dates, or just employee, i need all three. If i enter just dates i get the msgbox error from the code and if i just enter an emmployee i get a msgbox saying you must enter both dates. seems like we lost condition 1 and the employee search isnt working at all even with dates filled in? here is the current code:

    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 & "*'")
    Exit Sub
EH:
    MsgBox "There was an error with the search!  " & _
        "Please contact your Database Administrator.", vbCritical, "WARNING!"
    Exit Sub
End If

[twinnyfo]

Please post the entire procedure--from "Private Sub..." through "End Sub".

[didacticone]

That is the entire procedure- i just didnt include private sub and end sub

[twinnyfo]

Line 28 is what is causing problems for now. It's a stray "End If".

[didacticone]

its not related to the "If fSearch Then" on line 18?

[twinnyfo]

Should look like this:

    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

Not sure why things keep moving around. Post #18 had it correct.

[didacticone]

Probably my fault, i have been trying different things and perhaps posted the wrong thing. So the exact code i have now is:

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

The issue is, it requires all three fields to have data (employee, begindate, enddate) otherwise it brings up a msgbox.

If all three fields have data it will work.

[twinnyfo]

What scenarios have to tried, and which MsgBox is being desplayed for those scenarios? This will help us look for the problem.........

Have you stepped through the code when it is executing to see what is happening? To do this, in your VBA editor, hover your mouseon the left, gray margin and click to the left of "Private Sub". That will place a heavy dot in the margin.

When you open the form and run the code, the VBA editor will highlight that line. Then press F8 to move through the code step-by-step. While you are doing this, you can hover your mouse over each variable and object to get a pop-up of the value.

[didacticone]

if employee is left blank but both date fields are filled in, it returns:
MsgBox "There was an error with the search! " & _
"Please contact your Database Administrator.", vbCritical, "WARNING!"

If employee is filled in and the dates are left blank it returns: "you must enter both dates"

[didacticone]

I will step through the code as you suggested now as well and see what i can find out

[twinnyfo]

Do me a favor and use this:

Private Sub Command22_Click()
    On Error GoTo EH
    Dim fSearch     As Boolean
    Dim strFilter   As String
 
    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
        strFilter = 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 & "*'")
        Debug.Print strFilter
        Me.Filter = strFilter
        Me.FilterOn = True
    End If
 
    Exit Sub
EH:
    MsgBox "There was an error with the search!  " & _
        "Please contact your Database Administrator.", vbCritical, "WARNING!"
    Exit Sub
 
End Sub

Then, copy and paste what shows up in your immediate window. This is a better method to troubleshoot.

[didacticone]

I pasted exactly what you put and received no immediate error with the code.

When trying to run it on the form i'm receiving the same msgboxes as before

[twinnyfo]

Have you had any luck stepping through the code? There is something obvoius that we are missing.

I can't test this as I don't have your form. From everything I see in the code. It should work, unless you have incorrect names for your text boxes.

Try changing the Error handling to this:

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

That will tell us what error you are running into.

[didacticone]

OK when i try to enter just dates and no employee i get the following:

There was an error with the search!

Syntax error (missing operator) in query expression '[date1]
BBETWEEN #3/1/2018# AND #3/31/2018# AND'.

Please contact your database administrator

[didacticone]

When i enter only an employee and leave the dates blank i get the msgbox saying "you must enter both dates"

[twinnyfo]

OK - now we're getting somewhere! I've identified the problem, it just took seeing the Filter String that was being produced to identify it.

Our order of If-Thens is out of place. If we don't want any dates, we are forcing the dates. And if we have only dates, it's always assuming we have an employee.

This should be the fix--look closely at the order and I think you will understand...........

Private Sub Command22_Click()
    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
End Sub

Try running with all scenarios, make note of all Msgboxes, errors and the Filter String listed in your Immediate window.

[didacticone]

It worked! Awesome... now im going to study to see what youre talking about because i was trying a bunch of different combos but couldnt get it.

Thank you so much for your help with this!

[twinnyfo]

Glad we could get this working for you. Hope you have a great weekend!

[didacticone]

Me too! Thanks again for all the help... and hope you have a nice weekend as well!