Me.Filter query

[wackywoo105]

I have a field for first and last name. I can currently filter records based on last name. I want to make it so I can also type in the first name or initial after the full or part of the surname and it will filter appropriately.

e.g. Paul Clarke would be shown if I typed “Cla Pa” and clicked filter.

Here is the code I have been playing with. The debug shows the names are pulled out ok. Im struggling to write the Me.Filter query. I just says no current record. I haven’t yet put the wildcards in so I could just type “c p” into the filter text box and it would return Paul Clarke.

Private Sub FilterON_Click()
Me.FilterON = False
Dim nSpace As Integer
nSpace = InStrRev(Me![Filter], " ")
If nSpace = 0 Then
Me.Filter = "[surname] like '" & Me![Filter] & "*'"
GoTo nospace
End If
Dim last As String
Dim first As String
last = Left(Me![Filter], InStr(Me![Filter], " ") - 1)
first = Right(Me![Filter], Len(Me![Filter]) - InStrRev(Me![Filter], " "))
Debug.Print last
Debug.Print first
Me.Filter = "[Surname] like " & last & " And [First Names] like " & first
nospace:
Me.FilterON = True
End Sub

Any help on this would be greatly appreciated. Also I have no idea of placing quotation marks etc. Is there any online resource I could refer to so I can learn?

 

[pr2-eugin]

Please use Code Tags when posting VBA Code !!

Private Sub FilterON_Click()
    Dim nSpace As Integer, lastStr As String, firstStr As String
    
    Me.FilterON = False
    
    nSpace = InStrRev(Me![Filter], " ")
    
    If nSpace = 0 Then
        Me.Filter = "[surname] LIKE '" & Me![Filter] & "*'"
    Else
        lastStr = Trim(Mid(Me![Filter], 1, InStr(Me![Filter], " ")))
        firstStr = Trim(Mid(Me![Filter], InStr(Me![Filter], " ")))
        
        Debug.Print lastStr
        Debug.Print firstStr
        
        Me.Filter = "[Surname] LIKE [COLOR=Red][B]'[/B][/COLOR]" & lastStr & "[COLOR=Red][B]*'[/B][/COLOR] And [First Names] LIKE [COLOR=Red][B]'[/B][/COLOR]" & firstStr [COLOR=Red][B]& "*'"[/B][/COLOR]
    End If
    
    Me.FilterON = True
End Sub

 

[wackywoo105]

Thanks for your quick reply. Post edited. Just tried and works perfectly.

 

[pr2-eugin]

Thanks for your quick reply. Post edited. Just tried and works perfectly.

You're welcome. Just that you know, the main reason we ask you to edit/post code using CODE tags is to make it readable. THIS is what I mean.

 

[wackywoo105]

Sorry to open this up again but I have hit a snag. The code is working great apart from one issue. If I search a name such as Paul O'Callaghan by typing "o'c pa" I receive a Run-time error. It says Syntax error (missing operator) in query expression '[surname] LIKE 'o'c*' And [First Names] LIKE 'pa*".

Is there a way getting it to work with the apostrophe or putting in an error handler or code to remove it?

 

[Brianwarnock]

try using double quotes instead of the single quote, they are interchangeable, of course if your string can contain " and /or ' then the issue becomes more complex and you have to use char(34) approach, search Google

Brian

 

[Brianwarnock]

You might like this

http://www.techrepublic.com/blog/microsoft-office/5-rules-for-embedding-strings-in-vba-code/#.

Brian

 

[wackywoo105]

I've had a play around with double and double-double quotes and char(34) etc. but nothing seems to be working.

 

[Brianwarnock]

Hmm, I'm out of ideas and no longer have Access to play with.
I will put a message out to other VIPs for help

Brian

 

[namliam]

Try
"[surname] LIKE ""o'c*"" And [First Names] LIKE ""pa*"""

 

[pr2-eugin]

I've had a play around with double and double-double quotes and char(34) etc. but nothing seems to be working.

I seriously cannot make sense of what you exactly mean by "its not working"

 

[wackywoo105]

I seriously cannot make sense of what you exactly mean by "its not working"

I either get errors when the code runs or it just results in no current record for any search. I am not sure where to place them correctly so have played around quite a bit but none of what I have tried works.

 

[pr2-eugin]

Show the latest code you have, which has failed and also highlight the line of code that is throwing an error with the error number and error description.

 

[namliam]

Echo

Try
"[surname] LIKE ""o'c*"" And [First Names] LIKE ""pa*"""

 

[wackywoo105]

Try
"[surname] LIKE ""o'c*"" And [First Names] LIKE ""pa*"""

Thanks but this results in no current record so I guess I'm not implementing it correctly.

Me.Filter = "[Surname] LIKE '"" & lastStr & ""*' And [First Names] LIKE '"" & firstStr & ""*'"

 

[pr2-eugin]

Try this,

Me.Filter = "[Surname] LIKE " & Chr(34) & lastStr & "*" & Chr(34) & _
            " And [First Names] LIKE " & Chr(34) & firstStr & "*" & Chr(34)

 

[wackywoo105]

Try this,

Me.Filter = "[Surname] LIKE " & Chr(34) & lastStr & "*" & Chr(34) & _
            " And [First Names] LIKE " & Chr(34) & firstStr & "*" & Chr(34)

You sir are a bona fide genius. That appears to work perfectly. Thank you very much. I will have a good look at what you have written to see if I can work out where I was going wrong but in case I can’t could I be a pain and ask you to fix this one also.

Me.Filter = "[surname] LIKE '" & Me![Filter] & "*'"

edit: just tried this and it also appears to work.

Me.Filter = "[Surname] LIKE " & Chr(34) & Me![Filter] & "*" & Chr(34) & ""

 

[pr2-eugin]

I am glad you are leaning to do it on your own ! Good luck.

 

[namliam]

Thanks but this results in no current record so I guess I'm not implementing it correctly.

Me.Filter = "[Surname] LIKE '"" & lastStr & ""*' And [First Names] LIKE '"" & firstStr & ""*'"

This is the way you would have "my solution" made to work....
Me.Filter = "[Surname] LIKE """ & lastStr & "*"" And [First Names] LIKE """ & firstStr & "*"""

I hate using that nonsence chr() bullhockey