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

ID: Q198503

The information in this article applies to:

Microsoft Access 2000

Moderate: Requires basic macro, coding, and interoperability skills.

SYMPTOMS

When you use the Instr() function, you may see incorrect results when you search through a character string looking for certain ASCII characters, usually non-US characters.

CAUSE

There are two possible causes for this behavior:

The Option Compare Database statement is in effect. This can cause unexpected results when you search for any character whose ASCII value is 128 or greater.

-or-

You are searching for certain characters, even with the Option Compare Binary or Option Compare Text statements in effect. These characters are not affected by the Option Compare setting when you use the Instr() function.

These characters include, but are not limited to the following:


   Upper- and lower-case "A" with umlaut     (ASCII 196 and 228)
   Upper- and lower-case "AE"                (ASCII 198 and 230)
   Upper- and lower-case "N" with tilde      (ASCII 209 and 241)
   Upper- and lower-case "O" with umlaut     (ASCII 214 and 246)
   Upper- and lower-case "U" with umlaut     (ASCII 220 and 252)
   German double-"s" "ess-szet" character    (ASCII 223)

The Instr() function incorrectly returns a match on the first alphabetical character that resembles one of these special characters. For example, searching for "A" with an umlaut matches any letter "A."

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

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact a Microsoft Certified Solution Provider or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Solution Providers, please see the following page on the World Wide Web:

http://www.microsoft.com/mcsp/

For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

http://www.microsoft.com/support/supportnet/overview/overview.asp

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

Start Microsoft Access.

Open an existing database, or create a new database.

Create a new module.

If they are not already there, type the following lines in the Declarations section:


Option Explicit
Option Compare Database

Type the following function:


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

Open the Immediate window, 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.

Type the following line in the Immediate window, and then press ENTER:
```
? NewInstr(Chr(223),"s") 
```
The result, 0, appears on the next line.

MORE INFORMATION

Steps to Reproduce Behavior

Start Microsoft Access and open the sample database Northwind.mdb.

Create a new module.

Type the following lines in the Declarations section if they are not already there:


Option Explicit
Option Compare Database

In the Immediate Window, type the following line, and then press ENTER:
```
? Instr(Chr(223),"s") 
```
Note that the incorrect result, 1, appears on the next line.

REFERENCES

For more information about the Option Compare statement, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type "Option Compare statement" in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

Additional query words:

Keywords : kbdta AccCon KbVBA
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbprb