Multi filter by text boxes

ECEK

Registered User.
Local time
Today, 23:43
Joined
Dec 19, 2012
Messages
717
I have my code for my filter as follows:
Code:
Private Sub textBox1_AfterUpdate()

    If IsNull(Me.textBox1) Then
        Me.FilterOn = False
    Else
        Me.Filter = "surname = """ & Me.textBox1 & """"
        Me.FilterOn = True
    End If
    Me.textBox2.SetFocus
    
    
End Sub

Two things:
1. How do I get the filter to accept part names ? ie If I entered LIN then my results would be LINK, LINDY, LINNER, BLINK. Im not sure where to put the wildcard "*"

2. As you can see from my code, the set focus moves to textBox2
I would like this to be an additional filter.

Something like:

Code:
If IsNull(Me.textBox1) Then
        Me.FilterOn = False
    Else
        Me.Filter = "firstname = """ & Me.textBox2 & """" & what is in textBox1
        Me.FilterOn = True
    End If
    Me.textBox2.SetFocus

I'm sure its just a case of my coding !! Unless what Im doing is the wrong way to go about this?

Thanks you lovely people.
 
Last edited:
hi ECEK

you would use the LIKE operator and use the * before and after any text/variable. Something like the below (untested);
Code:
Me.Filter = "firstname LIKE '*' &" & Me.textBox2 & "& '*' AND surname LIKE '*'&" & textBox1 & "&'*'"
 
I'm afraid that doesn't work.

What I'm trying to do is to filter my continuous form by surname and then by first name.
 
I also tried this:
Code:
Private Sub textBox2_AfterUpdate()
If IsNull(Me.textBox1) Then
        Me.FilterOn = False
    Else
        Me.Filter = "FirstName = """ & Me.textBox2 & """" & Me.Filter = "surname = """ & Me.textBox1 & """"
        Me.FilterOn = True
    End If
    Me.textBox2.SetFocus
    
End Sub
 
too many &'s :banghead:

try,
Code:
"firstname LIKE [COLOR="red"]'[/COLOR]*" & Me.textBox2 & "*[COLOR="red"]'[/COLOR] AND surname LIKE [COLOR="red"]'[/COLOR]*" & textBox1 & "*[COLOR="Red"]'[/COLOR]"
Make sure you don't miss the single quotes '
 
Hi
To wrap a string value in your Sql script, use single quote not double quote
 
Me.Filter = "FirstName = " & Chr(34) & Me.textBox2 & "*" & chr(34) & " And surname = " & chr(34) & Me.textBox1 & "*" & Chr(34)
 
Brilliant guys: For the record my code is as follows:
Code:
Private Sub textBox1_AfterUpdate()

    If IsNull(Me.textBox1) Then
        Me.FilterOn = False
    Else
        Me.Filter = "surname LIKE '*" & textBox1 & "*'"
        Me.FilterOn = True
    End If
    Me.textBox2.SetFocus
        
End Sub

Then:

Code:
Private Sub textBox2_AfterUpdate()
If IsNull(Me.textBox1) Then
        Me.FilterOn = False
    Else
        Me.Filter = "firstname LIKE '*" & Me.textBox2 & "*' AND surname LIKE '*" & textBox1 & "*'"
        Me.FilterOn = True
    End If
    
    
End Sub

This also works when I change my text boxes to combo boxes (which was going to be my next question.
Many thanks all.
 
you can have a separate sub on your form, then call it on the two textboxes afterupdate event (even add your combo).

private sub textbox1_afterupdate()
If Not IsNull(Me.textbox1) Then
Call subChangeFilter
else
Me.filterOn=false
end if
end sub

private sub textbox2_afterupdate()
call subChangeFilter
end sub

private sub subChangeFilter()
Me.Filter = "firstname LIKE '*" & Me.textBox2 & "*' AND surname LIKE '*" & textBox1 & "*'"
Me.FilterOn=True
end sub
 
Hi Guys just a quick addition.

I now have multiple filter criteria as illustrated in the code below, based on the code:

Code:
Private Sub textBox5_AfterUpdate()
If IsNull(Me.textBox2) Then
        Me.FilterOn = False
    Else
        Me.Filter = "firstname LIKE '*" & Me.textBox2 & "*' AND tp1 LIKE '*" & textBox5 & "*' AND PC LIKE '*" & textBox4 & "*' AND surname LIKE '*" & textBox1 & "*' AND A1 LIKE '*" & textBox3 & "*'"
        Me.FilterOn = True
        Me.textBox1.SetFocus
    End If
End Sub

What I would like to do is to be able to search from any one of the fields.

At present if surname is blank then you cannot search for the first name, similarly I cannot just go to any of the other fields and search for that criteria.

Does anybody know if this is possible?

Basically if all other fields are blank it will filter on the field that isn't.

At present there needs to be something in the other fields in order to get a result.
The order is

Surname (surname) textBox1
First Name (firstname) textBox2
Address Line 1 (A1) textBox3
Postcode (PC) textBox4
Telephone (Home) (tp1) textBox5

(although the code above is in a different order - so to speak)

Im a bit confused by this line too

If IsNull(Me.textBox?) Then

Does it matter which number textBox it is?

As always. Many thanks for your time. This will help numerous people if we can nail it.
 
Code:
private sub textbox1_afterupdate()
Call subChangeFilter
end sub

private sub textbox2_afterupdate()
call subChangeFilter
end sub

private sub textbox3_afterupdate()
call subChangeFilter
end sub

private sub textbox4_afterupdate()
call subChangeFilter
end sub

private sub textbox5_afterupdate()
call subChangeFilter
end sub



private sub subChangeFilter()
if trim(me.textbox1 & me.textbox2 & me.textbox3 & me.textbox4 & me.textbox5 & "") = "" then
me.filteron=false
else
Me.Filter = "Me.Filter = "firstname LIKE '*" & Me.textBox2 & "*' AND tp1 LIKE '*" & textBox5 & "*' AND PC LIKE '*" & textBox4 & "*' AND surname LIKE '*" & textBox1 & "*' AND A1 LIKE '*" & textBox3 & "*'"
Me.FilterOn=True
end if
end sub
 
Utterly Brilliant

Justa correction to the change filter

Code:
Private Sub subChangeFilter()
If Trim(Me.textBox1 & Me.textBox2 & Me.textBox3 & Me.textBox4 & Me.textBox5 & "") = "" Then
Me.FilterOn = False
Else
Me.Filter = "firstname LIKE '*" & Me.textBox2 & "*' AND tp1 LIKE '*" & textBox5 & "*' AND PC LIKE '*" & textBox4 & "*' AND surname LIKE '*" & textBox1 & "*' AND A1 LIKE '*" & textBox3 & "*'"

Me.FilterOn = True
End If
End Sub

Thank you so much everybody.
This is a brilliant tool for people to replicate.
Cheers All
 

Users who are viewing this thread

Back
Top Bottom