If you need to filter by a Text value, such as person’s name or a company name, you may run into this problem:
An error caused by an apostrophe in a Text string
This is because the apostrophe is a Text delimiter. The way I deal with this problem is to make a calculated field in which the problem field is stripped of characters that can cause problems in Text strings, and do the filtering on that field.
For example, the TrimmedTechnician field strips out problem characters from the Techician field:
A calculated field to strip out problem characters
The StripChars procedure is listed below; the list of bad characters can be edited as wished:
Public Function StripChars(strText As String) As String
'Strips a spaces and parentheses from a text string
'Created by Helen Feddema 10-15-97
'Modified by Ruud H.G. van Tol 6-18-99
'Last modified by Helen Feddema 18-Jun-2016
On Error GoTo ErrorHandler
Dim strTestString As String
Dim strTestChar As String
Dim lngFound As Long
Dim i As Integer
Dim strStripChars As String
strStripChars = " ()-'/\"
strTestString = strText
i = 1
Do While i <= Len(strTestString)
'Find a strippable character
strTestChar = Mid$(strTestString, i, 1)
lngFound = InStr(strStripChars, strTestChar)
If lngFound > 0 Then
strTestString = Left(strTestString, i - 1) & Mid(strTestString, i + 1)
Else
i = i + 1
End If
Loop
StripChars = strTestString
ErrorHandlerExit:
Exit Function
ErrorHandler:
MsgBox "Error No: " & Err.Number & "; Description: " & _
Err.Description
Resume ErrorHandlerExit
End Function
