June, I just tested the following. It worked fine with multiple quotes, apostrophes. It did not break.
Code: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).
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.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.
Works great thanks to you both !!
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 " "June, I just tested the following. It worked fine with multiple quotes, apostrophes. It did not break.
Code: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
plus I do get a null error on strText = Me.txtUnbound.Value
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.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
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...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.
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.
update - scratch the name search, sorted
Ok so I have pieced together this function
It works - only if " is typed in I get an error - not sure how to get around that...Code: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
update replaced *"" with *' and it works like itsme's method.
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
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.
* is the wildcard. Just need to make sure it is concatenated correctly. Could even use ampersands and quotes just for *
The full code I used is (no errors):
Code: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
I took about 4 minutes looking it over and it seems solid.