Filtering on Like*

[Sue Compelling]

Hi ALL

I have the following query (kindly provided by Michel) which works a treat,
insofar as if I enter WAYWARD in the street name it returns all records of
WAYWARD.

However, there will be mulitple instances where the address is actually a
"corner" of and the name in the street name may actually be:

Wayward Road & Short Street or
Short & Wayward
Short & Wayward Streets
Wayward


How do I change my filter to bring back all records Like "Wayward*" or
"Wayward" or "*Wayward*" or "*Wayward"

TIA



Private Sub txtStreetName_AfterUpdate()

Dim strFilterVal As String
If Not IsNull(Me.txtStreetName) Then
strFilterVal = Me.txtStreetName
Me.Filter = "StreetName = '" & strFilterVal & "'"
Me.FilterOn = True
Else
Me.FilterOn = False
End If

End Sub

 

[Ken Snell [MVP]]

Me.Filter = "StreetName Like *'" & strFilterVal & "*'"

 

[Mr. B]

Sue,

Change your criteria using your variable to something like this for getting
all records for "Wayward*" :
Like '" & strFilterVal & "*'

if you want "*Wayward*" then:
Like '*" & strFilterVal & "*'

Here it is in your code for "*Wayward":
Private Sub txtStreetName_AfterUpdate()

Dim strFilterVal As String
If Not IsNull(Me.txtStreetName) Then
strFilterVal = Me.txtStreetName
Me.Filter = "StreetName = Like '*" & strFilterVal & "'"
Me.FilterOn = True
Else
Me.FilterOn = False
End If

End Sub

-----
HTH
Mr. B
http://www.askdoctoraccess.com/
Doctor Access Downloads Page:
http://www.askdoctoraccess.com/DownloadPage.htm

 

[Sue Compelling]

Thanks Ken

Definitely what I was looking for ... had to sneak in another ' but all good
....

Final code was ... Like '*" & strFilterVal & "*'"

 

[Sue Compelling]

Thanks so much Mr B

And thanks for detailing the variations - it helped me understand how they
were compiled.

Used this one which gave me all variables

Like '*" & strFilterVal & "*'"

 

[Mr. B]

Gald to help.

Good luck with your project.

-----
HTH
Mr. B
http://www.askdoctoraccess.com/
Doctor Access Downloads Page:
http://www.askdoctoraccess.com/DownloadPage.htm

 

[Ken Snell [MVP]]

Thanks Ken

Definitely what I was looking for ... had to sneak in another ' but all
good

Oops, sorry about the typo!