Apostrophes in Text strings used for filtering


If you need to filter by a Text value, such as person’s name or a company name, you may run into this problem:

AW 1713-T1

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:

AW 1713-T2

A calculated field to strip out problem characters

The StripChars procedure is listed below; the list of bad characters can be edited as wished:

1
Public Function StripChars(strText As String) As String
1
'Strips a spaces and parentheses from a text string
1
'Created by Helen Feddema 10-15-97
1
<strong><em>'Modified by Ruud H.G. van Tol 6-18-99</em></strong>
1
<strong><em>'Last modified by Helen Feddema 18-Jun-2016</em></strong>

 

1
On Error GoTo ErrorHandler

 

1
   Dim strTestString As String
1
   Dim strTestChar As String
1
   Dim lngFound As Long
1
   Dim i As Integer
1
   Dim strStripChars As String

 

1
   strStripChars = " ()-'/"
1
   strTestString = strText

 

1
 

  i = 1

1
   Do While i &lt;= Len(strTestString)
1
<strong><em>     'Find a strippable character</em></strong>
1
     strTestChar = Mid$(strTestString, i, 1)
1
     lngFound = InStr(strStripChars, strTestChar)
1
     If lngFound &gt; 0 Then
1
       strTestString = Left(strTestString, i - 1) &amp; Mid(strTestString, i + 1)
1
     Else
1
       i = i + 1
1
     End If
1
   Loop

 

1
   StripChars = strTestString

 

1
ErrorHandlerExit:
1
   Exit Function

 

1
ErrorHandler:
1
   MsgBox "Error No: " &amp; Err.Number &amp; "; Description: " &amp; _
1
     Err.Description
1
   Resume ErrorHandlerExit

 

1
End Function

Want More?

Office Watch has the latest news and tips about Microsoft Office.  Delivered once a week.