Filter On Split Form

[moke123]

I think you may be looking for something more along the lines of

dim strCrit as string

If not IsNull(txtCitNumFilter) Then
strCrit  = strCrit &  "And [Citation Num] = """  & txtCitNumFilter & """"
End If

If not IsNull(txtClerkFilter)  Then
strCrit  = strCrit & "AND [Clerk] = """ & txtClerkFilter & """"
End If

'strip off the leading "And"
If nz(strCrit,"")<>"" then
strcrit = mid(strcrit,4)
end if

debug.print strCrit

me.filter = strcrit
me.filteron = true

This is aircode so may need adjustments, wildcards or delimiting, but hopefully you get the idea.

Doing it with "Like" may look like this

strCrit  = strCrit & "And [Citation Num] Like ""*"  & txtCitNumFilter & "*"""

You may also want to use the Me keyword as in Me.txtCitNumFilter

[data808]

Ok its getting closer. I got it to work somewhat. The problem I am having now is that I have added a 3rd text box to be filtered and so it works fine if all 3 (Citation Num, Clerk, and Ref Num) have values typed into the text boxes. However, if text box 2 (Clerk) has no value in it then it will only filter text box 3 (Ref Num). It's like it needs text box 2 (Clerk) to bridge the gap for all 3 to be filtered. Here is the code:

Dim searchfor As String
Dim search1 As String
Dim search2 As String
Dim search3 As String
'Dim search4 As String
 
 If IsNull(txtCitNumFilter) = False Then
       search1 = Me.txtCitNumFilter.Value
       searchfor = ""
       'searchfor = "[Citation Num] = '" & search1 & "'"
       searchfor = "[Citation Num] Like '*" & search1 & "*'"
 End If
 If IsNull(txtClerkFilter) = False Then
      search2 = Me.txtClerkFilter.Value
      searchfor = searchfor & "AND [Clerk] Like '*" & search2 & "*'"
            If IsNull(txtCitNumFilter) = True Then
            searchfor = ""
            searchfor = searchfor & "[Clerk] like '*" & search2 & "*'"
            End If
 End If
 If IsNull(txtRefFilter) = False Then
      search3 = Me.txtRefFilter.Value
      searchfor = searchfor & "AND [Ref Num] Like '*" & search3 & "*'"
           If IsNull(txtClerkFilter) = True Then
           searchfor = ""
           searchfor = searchfor & "[Ref Num] Like '*" & search3 & "*'"
           End If
 End If

 Me.Filter = searchfor
 Me.FilterOn = True

[Welshgasman]

You would need a space before each AND?

AND Debug.Print searchfor
Also please start using code tags and indenting your code.

[data808]

Sorry about that on the code tags. Is that ok? I edited the above post.

[data808]

I think you may be looking for something more along the lines of

dim strCrit as string

If not IsNull(txtCitNumFilter) Then
strCrit  = strCrit &  "And [Citation Num] = """  & txtCitNumFilter & """"
End If

If not IsNull(txtClerkFilter)  Then
strCrit  = strCrit & "AND [Clerk] = """ & txtClerkFilter & """"
End If

'strip off the leading "And"
If nz(strCrit,"")<>"" then
strcrit = mid(strcrit,4)
end if

debug.print strCrit

me.filter = strcrit
me.filteron = true

This is aircode so may need adjustments, wildcards or delimiting, but hopefully you get the idea.

Doing it with "Like" may look like this

strCrit  = strCrit & "And [Citation Num] Like ""*"  & txtCitNumFilter & "*"""

You may also want to use the Me keyword as in Me.txtCitNumFilter

Sorry just saw your post now. Thank you for helping with this and making your suggestion but before I try out your code, would you be able to take a look at what I have created? I feel like I'm really close and would like to stick with what I have if its even possible to achieve what I need it to do. Right now I am having issues with having some text boxes null, text box 2 (Clerk) in particular. Without a value in the Clerk text box, the Citation Num and Ref Num text boxes won't filter together. It will just ignore Citation Num and filter the Ref Num text box. Here is the code:

Dim searchfor As StringDim search1 As String
Dim search2 As String
Dim search3 As String
'Dim search4 As String
 
 If IsNull(txtCitNumFilter) = False Then
       search1 = Me.txtCitNumFilter.Value
       searchfor = ""
       'searchfor = "[Citation Num] = '" & search1 & "'"
       searchfor = "[Citation Num] Like '*" & search1 & "*'"
 End If
 If IsNull(txtClerkFilter) = False Then
      search2 = Me.txtClerkFilter.Value
      searchfor = searchfor & "AND [Clerk] Like '*" & search2 & "*'"
            If IsNull(txtCitNumFilter) = True Then
            searchfor = ""
            searchfor = searchfor & "[Clerk] like '*" & search2 & "*'"
            End If
 End If
 If IsNull(txtRefFilter) = False Then
      search3 = Me.txtRefFilter.Value
      searchfor = searchfor & "AND [Ref Num] Like '*" & search3 & "*'"
           If IsNull(txtClerkFilter) = True Then
           searchfor = ""
           searchfor = searchfor & "[Ref Num] Like '*" & search3 & "*'"
           End If
 End If

I am thinking I need to cover more null scenarios into this code so that the user doesn't need to fill in each text box in order have the filter work for only what value criteria is given. I have a total of 9 text boxes and I know for sure that only some of these will be filled in at any given time so having to fill out each text box would not be practical. Thanks for the help and let me know if you have any suggestions on the existing code I have.

[Welshgasman]

Start with one control, and take each in turn?
Why are you checking txtcitnumfilter when processing txtclerkfilter?

Just process one at a time building the string.
Test if you have anything in the string before you add an AND.
That is only needed if there is already some criteria.

Walk through your code with F8 and use debug.print !!!

[data808]

Start with one control, and take each in turn?
Why are you checking txtcitnumfilter when processing txtclerkfilter?

Just process one at a time building the string.
Test if you have anything in the string before you add an AND.
That is only needed if there is already some criteria.

Walk through your code with F8 and use debug.print !!!

Ok I added that Debug.Print searchfor line and nothing happened but I assume that will come in handy if there is an error. Correct me if I'm wrong.

As for the checking of txtCitNumFilter while processing the txtClerkFilter, not sure yet but that was in the code when I found something similar online. It could also explain why the filter will function if the text boxes are filled in if they are next to each other. Meaning, if text box 1 and 2 has value, it works. If text box 2 and 3 has value, it works. If all 3 has value it works. Only when I break it up it won't function properly. Meaning, if text box 1 and 3 has value, it will only filter text box 3. It will ignore text box 1's value. So I need to figure out a way to bridge that gap between text box 1 and 3 so that it can filter those two values.

Is there a way to check all text boxes for values and just filter those text boxes that actually have something typed into them? I think that would solve this problem but I have no idea on how to create that code.

[CarlettoFed]

Dim searchfor As String

If Not IsNull(Me.txtClerkFilter) Then

searchfor = " AND [Clerk] like ""*" & Me.txtClerkFilter & "*"""
If Not IsNull(Me.txtCitNumFilter) Then searchfor = searchfor & " AND [Citation Num] Like ""*" & Me.txtCitNumFilter & "*"""

End If
If Not IsNull(Me.txtRefFilter) Then searchfor = searchfor & " AND [Ref Num] Like ""*" & Me.txtRefFilter & "*"""
If searchfor = "" Then

Me.Filter = ""
Me.FilterOn = False

Else

Me.Filter = Mid(searchfor, 6)
Me.FilterOn = True

End If

[data808]

Dim searchfor As String

If Not IsNull(Me.txtClerkFilter) Then

searchfor = " AND [Clerk] like ""*" & Me.txtClerkFilter & "*"""
If Not IsNull(Me.txtCitNumFilter) Then searchfor = searchfor & " AND [Citation Num] Like ""*" & Me.txtCitNumFilter & "*"""

End If
If Not IsNull(Me.txtRefFilter) Then searchfor = searchfor & " AND [Ref Num] Like ""*" & Me.txtRefFilter & "*"""
If searchfor = "" Then

Me.Filter = ""
Me.FilterOn = False

Else

Me.Filter = Mid(searchfor, 6)
Me.FilterOn = True

End If

Thanks for the suggestion. This code does exactly the same thing as the one I have. If txtClerkFilter is blank then it will only filter txtRefFilter. This code is much shorter though, so that's nice. lol

[data808]

Dim searchfor As String

If Not IsNull(Me.txtClerkFilter) Then

searchfor = " AND [Clerk] like ""*" & Me.txtClerkFilter & "*"""
If Not IsNull(Me.txtCitNumFilter) Then searchfor = searchfor & " AND [Citation Num] Like ""*" & Me.txtCitNumFilter & "*"""

End If
If Not IsNull(Me.txtRefFilter) Then searchfor = searchfor & " AND [Ref Num] Like ""*" & Me.txtRefFilter & "*"""
If searchfor = "" Then

Me.Filter = ""
Me.FilterOn = False

Else

Me.Filter = Mid(searchfor, 6)
Me.FilterOn = True

End If

Ok I fixed it. Seems like it was missing some End If's. Here is the code:

Dim searchfor As String

If Not IsNull(Me.txtClerkFilter) Then
    searchfor = " AND [Clerk] like ""*" & Me.txtClerkFilter & "*"""
End If


If Not IsNull(Me.txtCitNumFilter) Then
    searchfor = searchfor & " AND [Citation Num] Like ""*" & Me.txtCitNumFilter & "*"""
End If


If Not IsNull(Me.txtRefFilter) Then
    searchfor = searchfor & " AND [Ref Num] Like ""*" & Me.txtRefFilter & "*"""
End If


If searchfor = "" Then
    Me.Filter = ""
    Me.FilterOn = False
Else
    Me.Filter = Mid(searchfor, 6)
    Me.FilterOn = True
End If

I'm going to see if I can add another text box to this but would you be able to explain if I need to adjust this line: Me.Filter = Mid(searchfor, 6)

Don't understand what this does. Thanks.

[data808]

Ok its working very close to what I wanted. I'm now having 2 issues. The first is my start date and end date text boxes which would originally filter a date range and so the code would have to be adjusted for that but I think I would like to resolve this after my second issue.

The second issue I am having is a button that will undo all filters and it will also clear the values of all text boxes to make it fresh for the user to start typing new values into each text box. The part of the code that seems to be causing issues is this:

Me.txtCitNumFilter = ""
Me.txtCustNamFilter = ""
Me.txtClerkFilter = ""
Me.txtRefFilter = ""
Me.txtStartFilter = ""
Me.txtEndFilter = ""
Me.txtDLNumFilter = ""
Me.txtPlateFilter = ""
Me.cmbSubjectFilter = ""

After this part happens, the filter all button doesn't work properly. Even though the text boxes are all blank now after clicking the undo filter button, then I click on the filter all button just to test, it will still filter everything even if no values are entered. It's a very odd behavior that I don't understand why this happens. But if I don't click the undo filter button and just delete the values manually from my keyboard, the filter all button functions normally as it should. Meaning, if no values are entered in any text boxes and I click on the filter all button, nothing happens, which is what I want.

Can someone please help with this? Thanks.

[moke123]

Me.Filter = Mid(searchfor, 5)

Since each section of the filter string begins with " And ..." the code above removes the leading "And" in the final filter string.

[data808]

Me.Filter = Mid(searchfor, 5)

Since each section of the filter string begins with " And ..." the code above removes the leading "And" in the final filter string.

What does the number 5 do in that line?

In your original code the number was set at 6 with only 3 text boxes hooked up to the filter button. Now that I have 8 text boxes that it's filtering for, should that number be adjusted to fit the added on text boxes?

[data808]

Ok its working very close to what I wanted. I'm now having 2 issues. The first is my start date and end date text boxes which would originally filter a date range and so the code would have to be adjusted for that but I think I would like to resolve this after my second issue.

The second issue I am having is a button that will undo all filters and it will also clear the values of all text boxes to make it fresh for the user to start typing new values into each text box. The part of the code that seems to be causing issues is this:

Me.txtCitNumFilter = ""
Me.txtCustNamFilter = ""
Me.txtClerkFilter = ""
Me.txtRefFilter = ""
Me.txtStartFilter = ""
Me.txtEndFilter = ""
Me.txtDLNumFilter = ""
Me.txtPlateFilter = ""
Me.cmbSubjectFilter = ""

After this part happens, the filter all button doesn't work properly. Even though the text boxes are all blank now after clicking the undo filter button, then I click on the filter all button just to test, it will still filter everything even if no values are entered. It's a very odd behavior that I don't understand why this happens. But if I don't click the undo filter button and just delete the values manually from my keyboard, the filter all button functions normally as it should. Meaning, if no values are entered in any text boxes and I click on the filter all button, nothing happens, which is what I want.

Can someone please help with this? Thanks.

Ok I found a solution to this problem. The code needs to be written like this:

Me.txtCitNumFilter.Value = Null
Me.txtCustNamFilter.Value = Null
Me.txtClerkFilter.Value = Null
Me.txtRefFilter.Value = Null
Me.txtStartFilter.Value = Null
Me.txtEndFilter.Value = Null
Me.txtDLNumFilter.Value = Null
Me.txtPlateFilter.Value = Null
Me.cmbSubjectFilter.Value = Null

Then all is well in the world. lol

Now just figure out the date range issue. If anyone is able to help with that I'd appreciate it. Basically have a start date text box and end date text box. Originally created to filter a date range. I have added it to follow the same format in writing the code to filter it but I know this is not correct. It basically conflicts with each other and causes no records to show. I will mess with it a bit and see if I can get it to work but in the meantime, here is the code that I will be working on for the start date and end date text boxes:

Dim searchfor As String


If Not IsNull(Me.txtClerkFilter) Then
searchfor = " AND [Clerk] like ""*" & Me.txtClerkFilter & "*"""
End If


If Not IsNull(Me.txtCitNumFilter) Then
searchfor = searchfor & " AND [Citation Num] Like ""*" & Me.txtCitNumFilter & "*"""
End If


If Not IsNull(Me.txtRefFilter) Then
searchfor = searchfor & " AND [Ref Num] Like ""*" & Me.txtRefFilter & "*"""
End If


If Not IsNull(Me.txtCustNamFilter) Then
searchfor = searchfor & " AND [Customer Name] Like ""*" & Me.txtCustNamFilter & "*"""
End If


If Not IsNull(Me.txtStartFilter) Then
searchfor = searchfor & " AND [Date Of Service] Like ""*" & Me.txtStartFilter & "*"""
End If


If Not IsNull(Me.txtEndFilter) Then
searchfor = searchfor & " AND [Date Of Service] Like ""*" & Me.txtEndFilter & "*"""
End If


If Not IsNull(Me.cmbSubjectFilter) Then
searchfor = searchfor & " AND [Subject] Like ""*" & Me.cmbSubjectFilter & "*"""
End If


If Not IsNull(Me.txtDLNumFilter) Then
searchfor = searchfor & " AND [DL Num] Like ""*" & Me.txtDLNumFilter & "*"""
End If


If Not IsNull(Me.txtPlateFilter) Then
searchfor = searchfor & " AND [Plate Num] Like ""*" & Me.txtPlateFilter & "*"""
End If


If searchfor = "" Then
Me.Filter = ""
Me.FilterOn = False
Else
Me.Filter = Mid(searchfor, 6)
Me.FilterOn = True
End If

[CarlettoFed]

The code you posted doesn't do what you initially asked for, ie if Me.txtClerkFilter is null it must not consider what is written in Me.txtCitNumFilter.
For the problem of dates you should first read something about how filters are managed based on the type of data on which the search must be performed, in the forums or other you can find infinite cases where it is explained.
Furthermore, the Like type search cannot be used in Date type fields, it should not be used in the Numeric field but only in the Text type and in indispensable cases as it is very costly in terms of response times.