ACC: Using Find Method to
Find a Quotation Mark or Apostrophe
|
The
information in this article applies to:
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.
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
However, the following syntax will generate a syntax error because of the
apostrophe in the company name:
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
To search for a company name that includes an apostrophe, replace the single
quotation marks around the company name with two sets of double quotation
marks, as in the following example:
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
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
To search for an item containing quotation marks, concatenate Chr(34) with the
quotation marks, as in the following example:
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
For more information about the Find method, search for
"find method" using the Microsoft Access Help menu.