ACC: Using Find Method to Find a Quotation Mark or Apostrophe

• Microsoft Access versions 1.0, 1.1, 2.0

SUMMARY

Advanced: Requires expert coding, interoperability, and multiuser skills.

This article demonstrates how to use the Find method to search for text strings containing quotation marks (") or apostrophes ('). Searching for text strings containing these characters requires special syntax because the quotation mark and the apostrophe are used as delimiters in Access Basic.

MORE INFORMATION

Notes

• This article assumes that you are familiar with Access Basic and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information on Access Basic, please refer to the "Introduction to Programming" manual in Microsoft Access version 1.x, or the "Building Applications" manual in version 2.0.
• In the following sample code, an underscore (_) is used as a line- continuation character. Remove the underscore when re-creating this code in Access Basic.

How to Find an Apostrophe

The following examples use the Customers table in the sample database NWIND.MDB. Several of the company names in the Customers table contain apostrophes. If you are recreating these examples in Microsoft Access version 2.0, replace the company name "Babu Ji's Exports" in the examples with the company name "B's Beverages."

To find a specific company, you would normally use the following syntax:

   Function FindaPost1 ()
      Dim MyDB As Database, MyDynaset As Dynaset
      Set MyDB = CurrentDB()
      Set MyDynaset = MyDB.CreateDynaset("Customers")
      'Find this company.
      MyDynaset.FindFirst "[company name]='Around the Horn'"
      If Not MyDynaset.nomatch Then
         MsgBox MyDynaset.[company name]
      Else
         MsgBox "No Match"
      End If
      MyDynaset.Close
   End Function

   Function FindaPost2 ()
      Dim MyDB As Database, MyDynaset As Dynaset
      Set MyDB = CurrentDB()
      Set MyDynaset = MyDB.CreateDynaset("Customers")
      'The following line will generate an error.
      MyDynaset.FindFirst "[company name]='Babu Ji's Exports'"
      If Not MyDynaset.nomatch Then
         MsgBox MyDynaset.[company name]
      Else
         MsgBox "No Match"
      End If
      MyDynaset.Close
   End Function

   Function FindaPost3 ()
      Dim MyDB As Database, MyDynaset As Dynaset
      Set MyDB = CurrentDB()
      Set MyDynaset = MyDB.CreateDynaset("Customers")
      'Find this company (with an apostrophe in the name).
      MyDynaset.FindFirst "[company name]=""Babu Ji's Exports"""
      If Not MyDynaset.nomatch Then
         MsgBox MyDynaset.[company name]
      Else
         MsgBox "No Match"
      End If
      MyDynaset.Close
   End Function

How to Find a Quotation Mark

The following example uses the Employees table in the sample database NWIND.MDB. The Notes field for some employees contains quotation marks.

If you use the following syntax to find a string containing quotation marks, a compile error will be generated:

   Function FindQuote1 ()
      Dim MyDB As Database, MyDynaset As Dynaset
      Set MyDB = CurrentDB()
      Set MyDynaset = MyDB.CreateDynaset("Employees")
      'the following line generates a compile error.
      MyDynaset.FindFirst "[notes] like '*"The art of the cold_
              call."*'"
      If Not MyDynaset.nomatch Then
          MsgBox MyDynaset.[Last Name]
      Else
          MsgBox "No Match"
      End If
      MyDynaset.Close
   End Function

   Function FindQuote2 ()
      Dim MyDB As Database, MyDynaset As Dynaset
      Set MyDB = CurrentDB()
      Set MyDynaset = MyDB.CreateDynaset("Employees")
      'find this note that contains a quote.
      MyDynaset.FindFirst "[notes] like '*" & Chr(34) & "The art of_
              the cold call." & Chr(34) & "*'"
      If Not MyDynaset.nomatch Then
          MsgBox MyDynaset.[Last Name]
      Else
          MsgBox "No Match"
      End If
      MyDynaset.Close
   End Function

REFERENCES