By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,844 Members | 1,895 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,844 IT Pros & Developers. It's quick & easy.

VBA to filter dates and name field

100+
P: 243
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:
Expand|Select|Wrap|Line Numbers
  1. If Nz(Me.begindate) = vbNullString Or Nz(Me.enddate) = vbNullString Then
  2.     Debug.Print "The variable is Null or Zero-length"
  3.     MsgBox "Enter Dates"
  4.     Else
  5.  Me.Filter = "[date1] BETWEEN #" & Me.begindate & "# AND #" & Me.enddate & "#"
  6.  
  7.  Me.FilterOn = True
  8.  
  9.  End If
Expand|Select|Wrap|Line Numbers
  1. Me.Filter = "[employee] Like '" & Me.empsearch & "*'"
  2. Me.FilterOn = True
Is there a way to combine them so that it will filter both?

Thanks!
Mar 14 '18 #1

✓ answered by 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...........

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command22_Click()
  2.     On Error GoTo EH
  3.     Dim fSearch     As Boolean
  4.     Dim strFilter   As String
  5.  
  6.     fSearch = True
  7.  
  8.     If Nz(Me.empsearch) = vbNullString Then
  9.         If Nz(Me.begindate) = vbNullString _
  10.             And Nz(Me.EndDate) = vbNullString Then
  11.             MsgBox "You must enter search criteria"
  12.             fSearch = False
  13.         Else
  14.             If Nz(Me.begindate) = vbNullString _
  15.                 Or Nz(Me.EndDate) = vbNullString Then
  16.                 MsgBox "You must enter both dates"
  17.                 fSearch = False
  18.             End If
  19.         End If
  20.     Else
  21.         If Not (Nz(Me.begindate) = vbNullString _
  22.             And Nz(Me.EndDate) = vbNullString) Then
  23.             If Nz(Me.begindate) = vbNullString _
  24.                 Or Nz(Me.EndDate) = vbNullString Then
  25.                 MsgBox "You must enter both dates"
  26.                 fSearch = False
  27.             End If
  28.         End If
  29.     End If
  30.  
  31.     If fSearch Then
  32.         strFilter = IIf(Nz(Me.begindate) = vbNullString, "", _
  33.             "[date1] BETWEEN #" & Me.begindate & _
  34.                 "# AND #" & Me.EndDate & "#") & _
  35.             IIf(Nz(Me.empsearch) = vbNullString, "", _
  36.                 IIf(Nz(Me.begindate) = vbNullString, "", " AND ") & _
  37.                 "[employee] Like '*" & Me.empsearch & "*'")
  38.         Debug.Print strFilter
  39.         Me.Filter = strFilter
  40.         Me.FilterOn = True
  41.     End If
  42.  
  43.     Exit Sub
  44. EH:
  45.     MsgBox "There was an error with the search!  " & vbCrLf & vbCrLf & _
  46.         Err.Description & vbCrLf & vbCrLf & _
  47.         "Please contact your Database Administrator.", vbCritical, "WARNING!"
  48.     Exit Sub
  49. End Sub
Try running with all scenarios, make note of all Msgboxes, errors and the Filter String listed in your Immediate window.

Share this Question
Share on Google+
44 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,158
You should be able to simply combine the two sets of criteria:

Expand|Select|Wrap|Line Numbers
  1. Me.Filter = "[date1] BETWEEN #" & Me.begindate & "# AND #" & Me.EndDate & _
  2.     "# AND [employee] Like '" & Me.empsearch & "*'"
  3. Me.FilterOn = True
Hope this hepps!
Mar 14 '18 #2

100+
P: 243
So simple, but yet so difficult... thank you!!!
Mar 14 '18 #3

100+
P: 243
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?
Mar 14 '18 #4

100+
P: 243
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
Mar 14 '18 #5

twinnyfo
Expert Mod 2.5K+
P: 3,158
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.
Mar 14 '18 #6

100+
P: 243
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.
Mar 15 '18 #7

twinnyfo
Expert Mod 2.5K+
P: 3,158
I just used what you already had and included the search text box....

Expand|Select|Wrap|Line Numbers
  1. If Nz(Me.begindate) = vbNullString _
  2.     Or Nz(Me.EndDate) = vbNullString _
  3.     Or Nz(Me.empsearch) = vbNullString Then
  4.     MsgBox "Enter Dates and search criteria"
  5. Else
  6.     Me.Filter = "[date1] BETWEEN #" & Me.begindate & "# AND #" & Me.EndDate & _
  7.         "# AND [employee] Like '" & Me.empsearch & "*'"
  8.     Me.FilterOn = True
  9. End If
Mar 15 '18 #8

100+
P: 243
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.
Mar 15 '18 #9

twinnyfo
Expert Mod 2.5K+
P: 3,158
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:

Expand|Select|Wrap|Line Numbers
  1. If Nz(Me.begindate) = vbNullString _
  2.     Or Nz(Me.EndDate) = vbNullString Then
  3.     MsgBox "Enter Dates"
  4. Else
  5.     Me.Filter = "[date1] BETWEEN #" & Me.begindate & "# AND #" & Me.EndDate & "#" & _
  6.         IIf(Nz(Me.empsearch) = vbNullString,"", " AND [employee] Like '" & Me.empsearch & "*'")
  7.     Me.FilterOn = True
  8. End If
Hope that hepps!
Mar 15 '18 #10

100+
P: 243
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?
Mar 15 '18 #11

twinnyfo
Expert Mod 2.5K+
P: 3,158
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.
Mar 15 '18 #12

100+
P: 243
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command22_Click()
  2.  
  3. If Nz(Me.begindate) = vbNullString _
  4.     Or Nz(Me.enddate) = vbNullString _
  5.     Or Nz(Me.empsearch) = vbNullString Then
  6.     MsgBox "Enter Dates"
  7. Else
  8.     Me.Filter = "[date1] BETWEEN #" & Me.begindate & "# AND #" & Me.enddate & "#" & _
  9.         IIf(Nz(Me.empsearch) = vbNullString, "", " AND [employee] Like '" & Me.empsearch & "*'")
  10.     Me.FilterOn = True
  11.     End If
  12. 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
Mar 15 '18 #13

twinnyfo
Expert Mod 2.5K+
P: 3,158
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.
Mar 15 '18 #14

100+
P: 243
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.
Mar 15 '18 #15

100+
P: 243
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.
Mar 15 '18 #16

twinnyfo
Expert Mod 2.5K+
P: 3,158
Without writing the entire procedure, I will skeleton outline what you need to do:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command22_Click()
  2. On Error GoTo EH
  3.     Dim fSearch As Boolean
  4.  
  5.     fSearch = True
  6.  
  7.     If 'ONLY one Date is entered' Then
  8.         MsgBox "You must enter both dates"
  9.         fSearch = False
  10.     Else
  11.         If 'BOTH Dates Are EMPTY' Then
  12.             If 'Employee is empty' Then
  13.                 MsgBox "You must enter search criteria"
  14.                 fSearch = False
  15.             End If
  16.         End If
  17.     End If
  18.  
  19.     If fSearch Then
  20.         'Build Your Filter
  21.     End If
  22.  
  23.     Exit Sub
  24. EH:
  25.     MsgBox "There was an error with the search!  " & _
  26.         "Please contact your Database Administrator.", vbCritical, "WARNING!"
  27.     Exit Sub
  28. 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.
Mar 15 '18 #17

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

Expand|Select|Wrap|Line Numbers
  1. On Error GoTo EH
  2.     Dim fSearch As Boolean
  3.  
  4.     fSearch = True
  5.  
  6.     If Nz(Me.begindate) = vbNullString Or Nz(Me.enddate) = vbNullString Then
  7.         MsgBox "You must enter both dates"
  8.         fSearch = False
  9.     Else
  10.         If Nz(Me.begindate) = vbNullString And Nz(Me.enddate) = vbNullString Then
  11.             If Nz(Me.empsearch) = vbNullString Then
  12.                 MsgBox "You must enter search criteria"
  13.                 fSearch = False
  14.             End If
  15.         End If
  16.     End If
  17.  
  18.     If fSearch Then
  19.         Me.Filter = "[date1] BETWEEN #" & Me.begindate & "# AND #" & Me.enddate & "#" & _
  20.         IIf(Nz(Me.empsearch) = vbNullString, "", " AND [employee] Like '" & Me.empsearch & "*'")
  21.     Me.FilterOn = True
  22.     End If
  23.  
  24.     Exit Sub
  25. EH:
  26.     MsgBox "There was an error with the search!  " & _
  27.         "Please contact your Database Administrator.", vbCritical, "WARNING!"
  28.     Exit Sub
  29. 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?
Mar 15 '18 #18

twinnyfo
Expert Mod 2.5K+
P: 3,158
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.
Mar 15 '18 #19

100+
P: 243
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.
Mar 15 '18 #20

100+
P: 243
I tried something like this:

Expand|Select|Wrap|Line Numbers
  1. Me.Filter = "[date1] BETWEEN #" & iif(nz(Me.begindate) = vbnullstring, "", & "# AND #" & iif(nz(Me.enddate) = vbnullstring, "", & "#" & _
  2.         IIf(Nz(Me.empsearch) = vbNullString, "", " AND [employee] Like '" & Me.empsearch & "*'")
Im trying man, lol, just not totally grasping it yet.
Mar 15 '18 #21

twinnyfo
Expert Mod 2.5K+
P: 3,158
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:

Expand|Select|Wrap|Line Numbers
  1. Me.Filter = IIf(nz(Me.begindate) = vbnullstring, "", _
  2.     "[date1] BETWEEN #" & Me.begindate & "# AND #" & Me.enddate & "#" & _
  3.     IIf(nz(Me.begindate) = vbnullstring, "", " AND ") & 
  4.     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!
Mar 15 '18 #22

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

Expand|Select|Wrap|Line Numbers
  1. Me.Filter = IIf(nz(Me.begindate) = vbnullstring, "", _
  2.     "[date1] BETWEEN #" & Me.begindate & "# AND #" & Me.enddate & "#" & _
  3.     IIf(nz(Me.begindate) = vbnullstring, "", " AND IIf(nz(Me.enddate) = vbnullstring, "",) &
  4.  IIf(Nz(Me.empsearch) = vbNullString,"", " AND [employee] Like '" & Me.empsearch & "*'")
Mar 15 '18 #23

twinnyfo
Expert Mod 2.5K+
P: 3,158
Line 2:

Expand|Select|Wrap|Line Numbers
  1.     "[date1] BETWEEN #" & Me.begindate & "# AND #" & Me.enddate & "#") & _
I don't have the ability to run this code live, mso I forgot the closing parenthesis.
Mar 15 '18 #24

100+
P: 243
now it brings up a compile error: expected expression... i was looking through all the parenthesis also
Mar 15 '18 #25

twinnyfo
Expert Mod 2.5K+
P: 3,158
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 "&").
Mar 15 '18 #26

100+
P: 243
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:

Expand|Select|Wrap|Line Numbers
  1.     On Error GoTo EH
  2.     Dim fSearch As Boolean
  3.  
  4.     fSearch = True
  5.  
  6.     If Nz(Me.begindate) = vbNullString Or Nz(Me.enddate) = vbNullString Then
  7.         MsgBox "You must enter both dates"
  8.         fSearch = False
  9.     Else
  10.         If Nz(Me.begindate) = vbNullString And Nz(Me.enddate) = vbNullString Then
  11.             If Nz(Me.empsearch) = vbNullString Then
  12.                 MsgBox "You must enter search criteria"
  13.                 fSearch = False
  14.             End If
  15.         End If
  16.     End If
  17.  
  18.     If fSearch Then
  19. Me.Filter = IIf(Nz(Me.begindate) = vbNullString, "", _
  20.     "[date1] BETWEEN #" & Me.begindate & "# AND #" & Me.enddate & "#") & _
  21.     IIf(Nz(Me.begindate) = vbNullString, "", " AND ") & _
  22.     IIf(Nz(Me.empsearch) = vbNullString, "", "[employee] Like '" & Me.empsearch & "*'")
  23.     Exit Sub
  24. EH:
  25.     MsgBox "There was an error with the search!  " & _
  26.         "Please contact your Database Administrator.", vbCritical, "WARNING!"
  27.     Exit Sub
  28. End If
Mar 16 '18 #27

twinnyfo
Expert Mod 2.5K+
P: 3,158
Please post the entire procedure--from "Private Sub..." through "End Sub".
Mar 16 '18 #28

100+
P: 243
That is the entire procedure- i just didnt include private sub and end sub
Mar 16 '18 #29

twinnyfo
Expert Mod 2.5K+
P: 3,158
Line 28 is what is causing problems for now. It's a stray "End If".
Mar 16 '18 #30

100+
P: 243
its not related to the "If fSearch Then" on line 18?
Mar 16 '18 #31

twinnyfo
Expert Mod 2.5K+
P: 3,158
Should look like this:

Expand|Select|Wrap|Line Numbers
  1.     If fSearch Then
  2.         Me.Filter = IIf(Nz(Me.begindate) = vbNullString, "", _
  3.             "[date1] BETWEEN #" & Me.begindate & "# AND #" & Me.EndDate & "#") & _
  4.             IIf(Nz(Me.begindate) = vbNullString, "", " AND ") & _
  5.             IIf(Nz(Me.empsearch) = vbNullString, "", "[employee] Like '" & Me.empsearch & "*'")
  6.     End If
  7.  
  8.     Exit Sub
  9. EH:
  10.     MsgBox "There was an error with the search!  " & _
  11.         "Please contact your Database Administrator.", vbCritical, "WARNING!"
  12.     Exit Sub
  13. End Sub
Not sure why things keep moving around. Post #18 had it correct.
Mar 16 '18 #32

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

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command22_Click()
  2.     On Error GoTo EH
  3.     Dim fSearch As Boolean
  4.  
  5.     fSearch = True
  6.  
  7.     If Nz(Me.begindate) = vbNullString Or Nz(Me.enddate) = vbNullString Then
  8.         MsgBox "You must enter both dates"
  9.         fSearch = False
  10.     Else
  11.         If Nz(Me.begindate) = vbNullString And Nz(Me.enddate) = vbNullString Then
  12.             If Nz(Me.empsearch) = vbNullString Then
  13.                 MsgBox "You must enter search criteria"
  14.                 fSearch = False
  15.             End If
  16.         End If
  17.     End If
  18.  
  19.     If fSearch Then
  20.         Me.Filter = IIf(Nz(Me.begindate) = vbNullString, "", _
  21.             "[date1] BETWEEN #" & Me.begindate & "# AND #" & Me.enddate & "#") & _
  22.             IIf(Nz(Me.begindate) = vbNullString, "", " AND ") & _
  23.             IIf(Nz(Me.empsearch) = vbNullString, "", "[employee] Like '" & Me.empsearch & "*'")
  24.     End If
  25.  
  26.     Exit Sub
  27. EH:
  28.     MsgBox "There was an error with the search!  " & _
  29.         "Please contact your Database Administrator.", vbCritical, "WARNING!"
  30.     Exit Sub
  31.  
  32. 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.
Mar 16 '18 #33

twinnyfo
Expert Mod 2.5K+
P: 3,158
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.
Mar 16 '18 #34

100+
P: 243
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"
Mar 16 '18 #35

100+
P: 243
I will step through the code as you suggested now as well and see what i can find out
Mar 16 '18 #36

twinnyfo
Expert Mod 2.5K+
P: 3,158
Do me a favor and use this:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command22_Click()
  2.     On Error GoTo EH
  3.     Dim fSearch     As Boolean
  4.     Dim strFilter   As String
  5.  
  6.     fSearch = True
  7.  
  8.     If Nz(Me.begindate) = vbNullString Or Nz(Me.EndDate) = vbNullString Then
  9.         MsgBox "You must enter both dates"
  10.         fSearch = False
  11.     Else
  12.         If Nz(Me.begindate) = vbNullString And Nz(Me.EndDate) = vbNullString Then
  13.             If Nz(Me.empsearch) = vbNullString Then
  14.                 MsgBox "You must enter search criteria"
  15.                 fSearch = False
  16.             End If
  17.         End If
  18.     End If
  19.  
  20.     If fSearch Then
  21.         strFilter = IIf(Nz(Me.begindate) = vbNullString, "", _
  22.             "[date1] BETWEEN #" & Me.begindate & _
  23.                 "# AND #" & Me.EndDate & "#") & _
  24.             IIf(Nz(Me.begindate) = vbNullString, "", " AND ") & _
  25.             IIf(Nz(Me.empsearch) = vbNullString, "", _
  26.                 "[employee] Like '*" & Me.empsearch & "*'")
  27.         Debug.Print strFilter
  28.         Me.Filter = strFilter
  29.         Me.FilterOn = True
  30.     End If
  31.  
  32.     Exit Sub
  33. EH:
  34.     MsgBox "There was an error with the search!  " & _
  35.         "Please contact your Database Administrator.", vbCritical, "WARNING!"
  36.     Exit Sub
  37.  
  38. End Sub
Then, copy and paste what shows up in your immediate window. This is a better method to troubleshoot.
Mar 16 '18 #37

100+
P: 243
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
Mar 16 '18 #38

twinnyfo
Expert Mod 2.5K+
P: 3,158
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:

Expand|Select|Wrap|Line Numbers
  1. EH:
  2.     MsgBox "There was an error with the search!  " & vbCrLf & vbCrLf & _
  3.         Err.Description & vbCrLf & vbCrLf & _
  4.         "Please contact your Database Administrator.", vbCritical, "WARNING!"
  5.     Exit Sub
That will tell us what error you are running into.
Mar 16 '18 #39

100+
P: 243
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
Mar 16 '18 #40

100+
P: 243
When i enter only an employee and leave the dates blank i get the msgbox saying "you must enter both dates"
Mar 16 '18 #41

twinnyfo
Expert Mod 2.5K+
P: 3,158
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...........

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command22_Click()
  2.     On Error GoTo EH
  3.     Dim fSearch     As Boolean
  4.     Dim strFilter   As String
  5.  
  6.     fSearch = True
  7.  
  8.     If Nz(Me.empsearch) = vbNullString Then
  9.         If Nz(Me.begindate) = vbNullString _
  10.             And Nz(Me.EndDate) = vbNullString Then
  11.             MsgBox "You must enter search criteria"
  12.             fSearch = False
  13.         Else
  14.             If Nz(Me.begindate) = vbNullString _
  15.                 Or Nz(Me.EndDate) = vbNullString Then
  16.                 MsgBox "You must enter both dates"
  17.                 fSearch = False
  18.             End If
  19.         End If
  20.     Else
  21.         If Not (Nz(Me.begindate) = vbNullString _
  22.             And Nz(Me.EndDate) = vbNullString) Then
  23.             If Nz(Me.begindate) = vbNullString _
  24.                 Or Nz(Me.EndDate) = vbNullString Then
  25.                 MsgBox "You must enter both dates"
  26.                 fSearch = False
  27.             End If
  28.         End If
  29.     End If
  30.  
  31.     If fSearch Then
  32.         strFilter = IIf(Nz(Me.begindate) = vbNullString, "", _
  33.             "[date1] BETWEEN #" & Me.begindate & _
  34.                 "# AND #" & Me.EndDate & "#") & _
  35.             IIf(Nz(Me.empsearch) = vbNullString, "", _
  36.                 IIf(Nz(Me.begindate) = vbNullString, "", " AND ") & _
  37.                 "[employee] Like '*" & Me.empsearch & "*'")
  38.         Debug.Print strFilter
  39.         Me.Filter = strFilter
  40.         Me.FilterOn = True
  41.     End If
  42.  
  43.     Exit Sub
  44. EH:
  45.     MsgBox "There was an error with the search!  " & vbCrLf & vbCrLf & _
  46.         Err.Description & vbCrLf & vbCrLf & _
  47.         "Please contact your Database Administrator.", vbCritical, "WARNING!"
  48.     Exit Sub
  49. End Sub
Try running with all scenarios, make note of all Msgboxes, errors and the Filter String listed in your Immediate window.
Mar 16 '18 #42

100+
P: 243
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!
Mar 16 '18 #43

twinnyfo
Expert Mod 2.5K+
P: 3,158
Glad we could get this working for you. Hope you have a great weekend!
Mar 16 '18 #44

100+
P: 243
Me too! Thanks again for all the help... and hope you have a nice weekend as well!
Mar 16 '18 #45

Post your reply

Sign in to post your reply or Sign up for a free account.