ACC2002: Unable to Use FindFirst to Retrieve Value with Apostrophe (286242)


The information in this article applies to:

  • Microsoft Access 2002
This article was previously published under Q286242
Moderate: Requires basic macro, coding, and interoperability skills.

For a Microsoft Access 2000 version of this article, see 199056.

SYMPTOMS

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'"

This behavior also occurs if you use the FindFirst method in the AfterUpdate event procedure of a combo box, and you search for a value that contains an apostrophe. For example:

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

When you are typing 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.

MORE INFORMATION

Steps to Reproduce the Behavior

  1. Open the sample database Northwind.mdb.
  2. Use the AutoForm:Columnar Wizard to create a new form based on the Customers table.
  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. Set the OnClick property of the command button to the following event procedure:
    Private Sub FindFirst_Click()

  Dim rst As DAO.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. Close the Visual Basic Editor.
  7. Switch the form to Form view, and then click the command button. Note that you receive a message box that contains the "No match was found" message.
  8. Change the FindFirst syntax in the code example to the following:

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

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

REFERENCES

For more information about the BoundColumn property, click Microsoft Access Help on the Help menu, type bind a column from a list box, drop-down list box, or combo box in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.
Modification Type:MajorLast Reviewed:11/6/2003
Keywords:kbdta kbprb KB286242
©2003 Microsoft Corporation. All rights reserved.