ACC97: Unable to Use FindFirst to Retrieve Value w/ Apostrophe

• Microsoft Access 97

SYMPTOMS

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

When you use the FindFirst method, you receive a No match or a run-time error message.

This behavior occurs when you search for a value that contains an apostrophe. For example:

   rst.FindFirst "[CompanyName] = 'B's Beverages'"

   Me.RecordsetClone.FindFirst "[CompanyName] = "&Me![ComboboxNN]

CAUSE

Searches for text strings that contain quotation marks (") or apostrophes (') require special syntax because both characters are used as delimiters in Visual Basic for Applications.

RESOLUTION

Workaround for Using a Text String

If you are typing in your search argument, use double quotation marks in your search string to find values that contain single quotation marks and use single quotation marks in your search string to find values that contain quotation marks. For example, to search for a CompanyName that includes an apostrophe, replace the single quotation marks around the CompanyName value with two sets of double quotation marks, as in the following example:

   rst.FindFirst "[CompanyName] = ""B's Beverages"""

Workaround for Using a Combo Box

If you are using a combo box to find values that contain quotation marks or apostrophes, add the primary key to the combo box and make it the bound column. Hide the bound column. Then change your criteria so that it searches in the field that corresponds to the bound column in the combo box.

For more information about changing the bound column in a combo box, search the Help Index for "BoundColumn property," and then "Bind a column from a list box or combo box."

STATUS

This behavior is by design.

MORE INFORMATION

This section 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 the "Building Applications with Microsoft Access 97" manual.

Steps to Reproduce Behavior

1. Open the sample database Northwind.mdb.

2. Create a new form based on the Customers table by using the AutoForm: Columnar Wizard.

3. Open the form in Design view and add a command button to the form.

4. Change the Name property for the command button to FindFirst.

5. Place the following code in the OnClick event procedure of the command button:

         Private Sub FindFirst_Click()
   

           Dim rst As Recordset
           Dim strCriteria As String
   
           Set rst = Me.RecordsetClone
           rst.FindFirst "[CompanyName] = 'B''s Beverages'"
   
              If rst.NoMatch Then
                 MsgBox "No match was found."
   
              Else
   
                 Me.Bookmark = rst.Bookmark
   
              End If
            rst.Close
   
          End Sub
   
   

6. Switch the form to Form view, and click the command button. Note that you receive a message box containing the "No match was found." message.

7. Change the FindFirst syntax in the code example to the following:

         rst.FindFirst "[CompanyName] = ""B's Beverages"""
   

8. Switch the form to Form view, and click the command button. Note that the record is found this time.

REFERENCES

For more information about using the apostrophe in the FindFirst argument, please see the following article in the Microsoft Knowledge Base: