ACC: Problems When You Use Instr() to Find Special Characters (151164)

RESOLUTION

You can use one of the following two methods to work around this behavior.

Method 1

If the incorrect behavior is due to Cause 1 in the "Cause" section, place the Option Compare Binary statement at the top of your module.

Method 2

If the incorrect behavior occurs because you are searching for characters that are not affected by the Option Compare setting, follow the steps below to create a user-defined function to search for the special characters listed in Cause 2 in the "Cause" section.

This method assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

NOTE: Visual Basic for Applications is called Access Basic in Microsoft Access versions 1.x and 2.0. For more information about Access Basic, please refer to the "Introduction to Programming" manual in Microsoft Access version 1.x or the "Building Applications" manual in Microsoft Access version 2.0

1. Start Microsoft Access.
2. Open an existing database, or create a new database.
3. Create a new module.
4. Type the following lines in the Declarations section:

         Option Explicit
         Option Compare Database

5. Type the following function:
   
   NOTE: In the following sample code, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this code in Access Basic.

          Function NewInstr (strSearch As String, strWhat As String) As _
          Integer
          '
          ' This function searches for the character strWhat in the string
          ' strSearch. It uses the ASCII value of strWhat, and therefore is
          ' not subject to Microsoft Access translation of special characters
          ' and ligatures. It returns the integer position of the strWhat in
          ' strSearch. It returns 0 if either strSearch or strWhat is empty,
          ' or if strWhat cannot be found.
          '
          ' Note: If strWhat contains more than one character, only the first
          ' character is searched for.
   
          Dim iLen As Integer, i As Integer
          Dim iRetVal As Integer
   
          If IsNull(strSearch) Or IsNull(strWhat) Or _
          IsEmpty(strSearch) Or IsEmpty(strWhat) Then
             iRetVal = 0
          Else
             iRetVal = 0
             iLen = Len(strSearch)
             i = 1
             Do
                If Asc(Mid(strSearch, i, 1)) = Asc(strWhat) Then
                   iRetVal = I
                End If
                i = i + 1
             Loop While iRetVal = 0 And i <= iLen
          End If
          NewInstr = iRetVal
   
          End Function
   						

6. Open the Debug window (called the Immediate window in Microsoft Access versions 1.x and 2.0), type the following line, and then press ENTER:

   ? NewInstr(Chr(223),Chr(223))

   Note that the result, 1, appears on the next line.
   
   NOTE: You can generate the special characters directly by holding down the ALT key on your keyboard, typing the number 0 (zero) on the numeric keypad, followed by the ASCII code, and then releasing the ALT key. Make sure that NumLock is on before typing this. For example, to generate the ess-szet character, hold down the ALT key and type 0223 on the numeric keypad.
7. Type the following line in the Debug window, and then press ENTER:

   ? NewInstr(Chr(223),"s")

   The result, 0, appears on the next line.