Full Version: filter with punctuation
UtterAccess Forums > Microsoft® Access > Access Forms
Macros
Hi all, I have a form with a text box on it, when users type into the text box it filters the form to clients whose last name starts with what they type.
It works fine for normal names, but things like O'brien pose a problem, when a user puts in the ' it throws an error.
Here is the code that does the filtering, I'm sure there's probably some tiny thing I need to change to allow for ' marks in the filter.
CODE
Private Sub txtFilter_Change()
    Dim strText As String, intCount As Integer
    strText = Nz(Me![txtFilter].Text, "")
    If strText = "" Then
        DoCmd.RunCommand acCmdRemoveFilterSort
        Exit Sub
    End If
intCount = DCount("ContactID", "QContacts", "[LastName] Like '" & strText & "*'")
    If intCount = 0 Then
        MsgBox "There are no records meeting the specified criteria.", vbInformation, "Title"
        Me![txtFilter] = Left(strText, Len(strText) - 1)
        Exit Sub
    End If
    DoCmd.ApplyFilter , "[LastName] Like '" & strText & "*'"
    Me.txtFilter.SetFocus
    Me.txtFilter.SelStart = Len(strText)
End Sub

Regards
Brendan
GustiAllah
Replace ' with double " [""].
Note: It will not work with " char (eg: O"Brien). But U can do "Replace function" for unallowed char (") that user typed on textbox, before applying/creating filter.
And... I prefer to "On After_Update Event" [Not on Change]
CODE
Private Sub txtFilter_Change()
    Dim strText As String, intCount As Integer
    strText = Nz(Me![txtFilter].Text, "")
    If strText = "" Then
        DoCmd.RunCommand acCmdRemoveFilterSort
        Exit Sub
    End If
intCount = DCount("ContactID", "QContacts", "[LastName] Like """ & strText & "*""")
    If intCount = 0 Then
        MsgBox "There are no records meeting the specified criteria.", vbInformation, "Title"
        Me![txtFilter] = Left(strText, Len(strText) - 1)
        Exit Sub
    End If
    DoCmd.ApplyFilter , "[LastName] Like """ & strText & "*"""
    Me.txtFilter.SetFocus
    Me.txtFilter.SelStart = Len(strText)
End Sub
Macros
Thankyou Gusti, that was perfect, much appreciated
rendan
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.