Filter On Split Form

[data808]

In the header of a split form I have a few text boxes each with their own filter button. In the click event for each of these filter buttons, I have code that looks like this:



Me.Filter = "[Citation Num] Like '*" & Me.txtCitNumFilter & "*'"
Me.FilterOn = True
Me.Requery
Me.txtCitNumFilter.SetFocus

Now I would like to have another button that will filter all at once. It will take everything typed into each text box that is filled and filter them simultaneously but I am having issues figuring out how to write the code for it. I have tried these with no luck:

Me.Filter = "[Citation Num] Like '*" & Me.txtCitNumFilter & "*'"
Me.Filter = "[Clerk] Like '*" & Me.txtClerkFilter & "*'"
Me.FilterOn = True
Me.Requery
Me.txtCitNumFilter.SetFocus

This one just filters the clerk and totally ignores the citation number and so I tried these:

Me.Filter = "[Citation Num] Like '*" & Me.txtCitNumFilter & "*'" And _
Me.Filter = "[Clerk] Like '*" & Me.txtClerkFilter & "*'"
Me.FilterOn = True
Me.Requery
Me.txtCitNumFilter.SetFocus

Me.Filter = "[Citation Num] Like '*" & Me.txtCitNumFilter & "*'"
Me.FilterOn = True
Me.Filter = "[Clerk] Like '*" & Me.txtClerkFilter & "*'"
Me.FilterOn = True
Me.Requery
Me.txtCitNumFilter.SetFocus

Both of these did not work either. What am I missing here? Can someone please help? Thanks.

[Welshgasman]

You are replacing one filter criteria with another?

You need to test if a control has a value, and if it has, concatenate that to any existing filter.

Use Debug.Print to actually see what filter you do have, NOT what you think you have.

[data808]

Thanks. How would I go about doing that?

[Welshgasman]

Me.Filter = Me.Filter & " AND " & <your next filter>

[data808]

Me.Filter = Me.Filter & " AND " & <your next filter>

So does this also check if any of the text boxes are null as well? Not sure if that is necessary. I just bring it up because you mentioned about checking if the control has a value? Also will this line also take care of wild cards before and after the value entered by the user? I have about 8 or 9 text boxes each with their own filter buttons. This "filter all at once" button I am hoping to create will filter any of those text boxes simultaneously to minimize the results of the query. The more fields they enter values for and then clicking the "filter all at once" button can make the results show very specifically what the user wants to see. Is this possible with your line of code? Thanks for the help on this.

[Welshgasman]

So does this also check if any of the text boxes are null as well? Not sure if that is necessary. I just bring it up because you mentioned about checking if the control has a value? Also will this line also take care of wild cards before and after the value entered by the user? I have about 8 or 9 text boxes each with their own filter buttons. This "filter all at once" button I am hoping to create will filter any of those text boxes simultaneously to minimize the results of the query. The more fields they enter values for and then clicking the "filter all at once" button can make the results show very specifically what the user wants to see. Is this possible with your line of code? Thanks for the help on this.

No?
You add the test for empty controls.

I was just giving you the syntax of how to concatenate.

Each concatenation would be inside an IF statement for each control that you want to filter on.

Each filter string is constructed as you need it, be it = or Like "*" & Me.control & "*"

[data808]

Thank you very much for info. I will definitely try this out but I should warn you, I am very beginner level when it comes to VBA so we'll see how this goes. lol I'll keep you posted. Thanks again.

[Welshgasman]

There is always Google, my first place of call, always. Generally someone has done it before me.

[data808]

Ok so I tried doing something simple first without any if statements because I just wanted to see if I could filter at least 2 text boxes instead of just one. Ultimately I would like to filter 9 text boxes but baby steps first. lol So this is the code I came up with to try first:

Me.Filter = "[Citation Num] Like '*" & Me.txtCitNumFilter & "*'" And "[Clerk] like '*" & _
Me.txtClerkFilter & "*'"
Me.FilterOn = True
Me.Requery
Me.txtCitNumFilter.SetFocus

This ran into an error. Run-time error '13': Type mismatch

I'll try messing with other possibilities and let you know what results I get.

[data808]

I tried this one:

Me.Filter = Me.txtCitNumFilter & " and " & Me.txtClerkFilter
Me.FilterOn = True
Me.Requery
Me.txtCitNumFilter.SetFocus

This one gives a syntax error. Run-time error '3075': Syntax error (missing operator) in query expression

[Welshgasman]

I tend to put the criteria into a string variable for domain functions if not a single simple criteria.
Then I can debug.print them to see if I have the syntax correct.
Then when correct, I can use them in the function.
Plus, if you cannot see the error, you can copy and paste here or eslewhere, so someone can point out the error.?

Strings need to be surrounded by single quotes ' unless it contains a single quote, then triple double quotes works, I think?


Date literals with # and in mm/dd/yyyy or yyyy-mm-dd format


Numbers do not need anything

[data808]

I think I can see the error when I click debug. Here is the latest code I tried:

Dim citNum As String
Dim clrIni As String


citNum = Me.txtCitNumFilter.Value
clrIni = Me.txtClerkFilter.Value


Me.Filter = "[Citation Num] like '*" & citNum & "*'" And _
"[Clerk] like '*" & clrIni & "*'"
Me.FilterOn = True
Me.Requery
Me.txtCitNumFilter.SetFocus

This one gives the Run-time 13 Type mismatch error. When I click debug it points to this line

Me.Filter = "[Citation Num] like '*" & citNum & "*'" And _
"[Clerk] like '*" & clrIni & "*'"

I think I read an article that used # before and after the date but it was a specific date where as mine would be random because the user is typing in criteria to search by so the criteria will be dynamic. Also I haven't gotten to the date fields yet. Right now just focusing on getting this to work for citation numbers text box and clerk initials text box. Once I get this figured out I will move on to the next text box. Eventually there will be a start date text box and end date text box which could be tricky because I want to be able to do a filter regardless if both of those are filled in by the user. Example, the user might just enter the start date and click the filter all button and I would like it to just go by that date instead of a date range if they were to have entered the end date as well. Does that make sense? But thats getting far ahead for now as I see just something as simple as filtering 2 text boxes is quite a challenge. I thought I would have had the solution for this by now but I guess its more complex than I anticipated unfortunately. Thanks for the help though. Really appreciate your input. Hopefully someone can chime in and figure this out for us.

[data808]

Oh! I just tried this and it actually did something and didn't give an error:

Dim searchfor As String
Dim search1 As String
Dim search2 As String

If IsNull(txtCitNumFilter) = False Then
search1 = Me.txtCitNumFilter.Value
searchfor = ""
searchfor = "[Citation Num] = '" & search1 & "'"
End If
If IsNull(txtClerkFilter) = False Then
search2 = Me.txtClerkFilter.Value
searchfor = searchfor & "AND [Clerk] = '" & search2 & "'"
If IsNull(txtCitNumFilter) = True Then
searchfor = ""
searchfor = searchfor & "[Clerk] = '" & search2 & "'"
End If
End If


Me.Filter = searchfor
Me.FilterOn = True

This one shows the filter symbols at the top of the 2 columns that it's supposed to be filtering but it shows no results in the split form. So basically it's filtering everything and showing no results. Any thoughts on this? I'm thinking I need to mess with the text box names and maybe the titles of the data types for the backend table. Maybe I have them mixed up? In my backend table the citation number title for that data type is "Citation Num" and for the clerk initials its called just "Clerk". For the text boxes they are called "txtCitNumFilter" and "txtClerkFilter" if that helps.

[data808]

Oh I think its cause its missing the "Like" '* wildcard. Do you know how to add that to this code? Then it might work cause I'm not typed the exact match for the citation number because I want to see more than one result. I am typing a partial value so it shows me all records that start with the combination of characters that I type and I think this code is to look up very specific values that need to be an exact match.

[Welshgasman]

An example from a DlookUp()

dlookup("ID","Transactions","Description Like '" & "Mr *" & "'")