Filter On Split Form

[data808]

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.

Thank you very much for your time and trying to help me on these issues. On a technical point you are probably right. However, I have done some testing on my final version of this code and it seems to work exactly how I want regardless of the things you mentioned. I need to get this done soon and do not have the time to go researching as of right now. I may in the future but I really was hoping for someone that is very good at writing VBA to write something up for me the correct way. So unless you can rewrite this code and make it work how it's technically supposed to, I think this will have to do for now. Load times for filtering do not seem bad at the moment and I am dealing with about 58k records on a test environment. I don't see this ever going over 100k as I do archive records every year. So I am happy with what I have for now unless I get any glitches down the road, I'll be back to seek help. For now, this is the final code that seems to be working exactly how I want it:

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 both date fields are entered
If Not IsNull(Me.txtStartFilter) And Not IsNull(Me.txtEndFilter) Then
    searchfor = searchfor & " AND [Date Of Service] Between #" & Nz(Me.[txtStartFilter], _
    "1/1/1900") & "# AND #" & Nz(Me.[txtEndFilter], "12/31/2900") & "#"
End If
' if only start date is entered
If Not IsNull(Me.txtStartFilter) And IsNull(Me.txtEndFilter) Then
    searchfor = searchfor & " AND [Date Of Service] Like ""*" & Me.txtStartFilter & "*"""
End If
' if only end date is entered
If Not IsNull(Me.txtEndFilter) And IsNull(Me.txtStartFilter) 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

[Welshgasman]

Re read post #11 for dates.

Output for the debug.print is in the Immediate window (Ctrl + G)

[CarlettoFed]

And because he previously wrote: "Without a value in the Employee textbox, the Citation Num and Ref Num textboxes will not filter together. It will just ignore the Citation Num and filter the Ref Num textbox"

[data808]

Re read post #11 for dates.

Output for the debug.print is in the Immediate window (Ctrl + G)

Post #11 is a bit beyond me at the moment. Sorry. lol Hopefully I can learn what you are talking about one day but I fear it may be too hard for me to catch on.

As for the Debug.Print, I know you keep bringing that up and I have no idea what that does but I'll try doing a CTRL+G in the VBA window to see what you are talking about and if I can have some use for it.

Thanks for the help. The date code may be wrong with the "Like" instead of the "#" but for now its working. It filters the dates I want and that's pretty much the point of all this. I'll probably fix that in the future though when I have more time.

Thanks again and take care.

[data808]

And because he previously wrote: "Without a value in the Employee textbox, the Citation Num and Ref Num textboxes will not filter together. It will just ignore the Citation Num and filter the Ref Num textbox"

That was with one of the first codes I used. Now it works fine. I also want to say thank you very much for your help. It was your code that brought me to my final code which is working great. It's all thanks to you for writing something up for me that helped me see the pattern to write the rest. This is why this forum is so awesome. Thanks again for everything and take care.

[moke123]

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?

It's nothing to do with the text boxes, it is the number of characters from the left to start the string. As Gasman pointed out, my original aircode left out a leading space before "And"
So " And [Citation Num] Like . . ." would be 6. Count from the left to the first "["


The mid function is

Mid(string, Start,[Length])

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

Me.txtCitNumFilter.Value = Null

Thats correct as the test is for not null. If you use Me.txtCitNumFilter.Value = "" that is a zero length string and not a null value.

[Welshgasman]

Post #11 is a bit beyond me at the moment. Sorry. lol Hopefully I can learn what you are talking about one day but I fear it may be too hard for me to catch on.

As for the Debug.Print, I know you keep bringing that up and I have no idea what that does but I'll try doing a CTRL+G in the VBA window to see what you are talking about and if I can have some use for it.

Thanks for the help. The date code may be wrong with the "Like" instead of the "#" but for now its working. It filters the dates I want and that's pretty much the point of all this. I'll probably fix that in the future though when I have more time.

Thanks again and take care.

Oh!, you will have use for it believe me, especially if you want to find out what you actually have in a variable as opposed to what you think you have.

Start using it, it will save you so much time, especially on silly errors.

[Welshgasman]

As Gasman pointed out, my original aircode left out a leading space before "And"

Sorry Moke, I did not realise that was your code. Thought it was the O/P's

[moke123]

NP GMan.

For your dates you could do something along the lines of

Dim dteS, dteE

dteS = Nz(Me.dteStart, "1/1/1900")
dteE = Nz(Me.dteEnd, "1/1/2050")

searchfor = searchfor & " AND SomeDateField between #" & dteS & "# and #" & dteE & "#"

Of course substitute your field and control names in the above