Validate characters in textbox before more vba

[Ruegen]

June, I just tested the following. It worked fine with multiple quotes, apostrophes. It did not break.

Dim strWhere As String
Dim strText As String
strText = Me.txtUnbound.Value
strText = Replace(strText, "'", "''")
strWhere = "[SomeValue] LIKE '" & strText & "*'"

Me.FilterOn = False
Me.Filter = ""
Me.Filter = strWhere
Me.FilterOn = True

Going to play around with this - it's more getting to understand why this works for me and that is what I have to get my head around (in my own time).

[ItsMe]

The previous example I provided used an If Then statement to evaluate whether or not quotations or apostrophes exist in the string variable. It turns out, there is not a need to evaluate for the existence of " only for '

This latest example employs the Replace function that was recommended by June. It addresses the ' character.

It was me fiddling around with the previous code example that led me to discover VBA does not care about the " and then finally test this latest example that simply evaluates for ' and nothing else.

Clear as mud?

I, like June, have always avoided using " as literal text within a string for fear of fantastic things happening.

[Ruegen]

The previous example I provided used an If Then statement to evaluate whether or not quotations or apostrophes exist in the string variable. It turns out, there is not a need to evaluate for the existence of " only for '

This latest example employs the Replace function that was recommended by June. It addresses the ' character.

It was me fiddling around with the previous code example that led me to discover VBA does not care about the " and then finally test this latest example that simply evaluates for ' and nothing else.

Clear as mud?

I, like June, have always avoided using " as literal text within a string for fear of fantastic things happening.

Makes sense. *Starts shoveling mud.

[Ruegen]

Works great thanks to you both !!

[Ruegen]

June, I just tested the following. It worked fine with multiple quotes, apostrophes. It did not break.

Dim strWhere As String
Dim strText As String
strText = Me.txtUnbound.Value
strText = Replace(strText, "'", "''")
strWhere = "[SomeValue] LIKE '" & strText & "*'"

Me.FilterOn = False
Me.Filter = ""
Me.Filter = strWhere
Me.FilterOn = True

I've been working with this for a bit - how would I replace/filter multiple fields? and when I remove data I would have no search criteria so I would hope the records return to show all and not fields that show " "

plus I do get a null error on strText = Me.txtUnbound.Value

[June7]

Filter multiple fields by building a search criteria string.

http://www.allenbrowne.com/ser-62code.html

Or use parameterized query.

Apply the Replace function wherever needed.

[Ruegen]

Filter multiple fields by building a search criteria string.

http://www.allenbrowne.com/ser-62code.html

Or use parameterized query.

Apply the Replace function wherever needed.

yeah I have used that one before but without the replace function - it's a lot to do. I would love this as a global function somehow...

[Ruegen]

Filter multiple fields by building a search criteria string.

http://www.allenbrowne.com/ser-62code.html

Or use parameterized query.

Apply the Replace function wherever needed.

while I am here - if I use concatenation for two fields into one field and have that as a string - how come when I filter the concatenated name it doesn't filter ?

say Bill Murray

I type Bill - it filters

I type Bill Murray and the form no longer filters.

[Ruegen]

while I am here - if I use concatenation for two fields into one field and have that as a string - how come when I filter the concatenated name it doesn't filter ?

say Bill Murray

I type Bill - it filters

I type Bill Murray and the form no longer filters.

update - scratch the name search, sorted

[Ruegen]

Ok so I have pieced together this function

Dim strWhere As String                  'The criteria string.
Dim lngLen As Long                      'Length of the criteria string to append to.


Dim strTeacherName As String
Dim strTeacherEmail As String


    'Text field example. Use quotes around the value in the string.
    If Not IsNull(Me.txtName) Then
        strTeacherName = Me.txtName.Value
        strTeacherName = Replace(strTeacherName, "'", "''")
        strWhere = strWhere & "([TeacherName1] like ""*" & strTeacherName & "*"") AND "
    Else
    Me.FilterOn = False
     End If






    'Another text field example. Use Like to find anywhere in the field.
    If Not IsNull(Me.txtEmail) Then
        strTeacherEmail = Me.txtEmail.Value
        strTeacherEmail = Replace(strTeacherEmail, "'", "''")
        strWhere = strWhere & "([TeacherEmail] Like ""*" & strTeacherEmail & "*"") AND "
    Else
    Me.FilterOn = False
     End If


    '***********************************************************************
    'Chop off the trailing " AND ", and use the string as the form's Filter.
    '***********************************************************************
    'See if the string has more than 5 characters (a trailng " AND ") to remove.
    lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then     'Nah: there was nothing in the string.
        
    Else                    'Yep: there is something there, so remove the " AND " at the end.
        strWhere = Left$(strWhere, lngLen)
        'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
        'Debug.Print strWhere


        'Finally, apply the string as the form's Filter.
        Me.Filter = strWhere
        Me.FilterOn = True
    End If












End Function

It works - only if " is typed in I get an error - not sure how to get around that...

update replaced *"" with *' and it works like itsme's method.

[ItsMe]

So you have this now and no problems?

like '*" & strTeacherName & "*') AND "

Maybe
like '*" & strTeacherName & "*'") & " AND "

Or
like '*" & strTeacherName & "*' AND ")

Not sure how the suggestions will affect parsing the trailing AND. I am suggesting to add a space before AND

[Ruegen]

I used '*"

and

"*'

so that you can filter any where in the form

so say

st johns

type johns and the whole st johns comes up (noticed that after it didn't show johns.)

I will post the entire thing pretty soon.

It seems to work perfectly - my understanding is that the code removes the AND if it is a blank field.

[ItsMe]

* is the wildcard. Just need to make sure it is concatenated correctly. Could even use ampersands and quotes just for *

[Ruegen]

The full code I used is (no errors):

Function fncSearchTeacher()


Dim strWhere As String                  'The criteria string.
Dim lngLen As Long                      'Length of the criteria string to append to.


Dim strTeacherName As String
Dim strTeacherEmail As String


    'Text field example. Use quotes around the value in the string.
    If Not IsNull(Me.txtName) Then
        strTeacherName = Me.txtName.Value
        strTeacherName = Replace(strTeacherName, "'", "''")
        strWhere = strWhere & "([TeacherName1] like '*" & strTeacherName & "*') AND "
    Else
    Me.FilterOn = False
     End If






    'Another text field example. Use Like to find anywhere in the field.
    If Not IsNull(Me.txtEmail) Then
        strTeacherEmail = Me.txtEmail.Value
        strTeacherEmail = Replace(strTeacherEmail, "'", "''")
        strWhere = strWhere & "([TeacherEmail] Like '*" & strTeacherEmail & "*') AND "
    Else
    Me.FilterOn = False
     End If


    '***********************************************************************
    'Chop off the trailing " AND ", and use the string as the form's Filter.
    '***********************************************************************
    'See if the string has more than 5 characters (a trailng " AND ") to remove.
    lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then     'Nah: there was nothing in the string.
        
    Else                    'Yep: there is something there, so remove the " AND " at the end.
        strWhere = Left$(strWhere, lngLen)
        'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
        'Debug.Print strWhere


        'Finally, apply the string as the form's Filter.
        Me.Filter = strWhere
        Me.FilterOn = True
    End If












End Function

[ItsMe]

I took about 4 minutes looking it over and it seems solid.