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:

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

Want More?

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