How to Use FindFirst with Strings that Have Apostrophe in VB

MORE INFORMATION

How to Find String that May Contain Apostrophe But Not Quotation Mark

It is easy to find stings containing an apostrophe (a single quotation mark if you're sure there are no embedded double quotation marks in the string. All you need to do is use embedded double quotation marks inside the Criteria$ variable.

For example, to build the FindFirst Criteria$ to search for the value in a$ which may contain an apostrophe, use this syntax:

   Criteria$ = "author = """ & a$ & """"
   data1.recordset.findfirst Criteria$
				

Visual Basic recognizes two double quotation marks in a row as an embedded double quotation mark. If a$ = "O'Conner" the Criteria$ will now read:

   author = "O'Conner"
				

Because the value you are searching is surrounded by double quotation marks, the Microsoft Access database engine will deal correctly with the embedded apostrophe.

The solution is more complex, however, if the string could contain a double quotation mark as well as or instead of an apostrophe. The remainder of this article explains how to deal with this more complex situation.

How to Find String that May Contain Apostrophe or Double Quotation Mark

NOTE: Before you try this example, you need to go into Data Manager. Data Manager is located on the Window menu of the main Visual Basic menu. Below are the steps for using Data Manager to add an apostrophe and a double quotation mark to certain records in the Authors table:

1. In Data Manager, select the Open Database Access option from the File menu. Select the BIBLIO.MDB database by double-clicking it. This will open the Microsoft Access BIBLIO.MDB database.
2. Select the Authors table, and choose the Open button.
3. From the Open window, use the Data control at the bottom to move to the record with the Author field is:

      Atre, Shaku
   						

4. Enter a apostrophe after the letter e so it looks like this:

      Atre', Shaku
   						

5. Use the Data control to select the next record, and choose Yes to commit the changes.
6. Move two more records to the record where the Author field is:

      Brackett, Michael H.
   						

7. Place a double quotation mark at the end of this field, so it looks like this:

      Brackett, Michael H."
   						

8. Use the Data control to select the next record, and choose Yes to commit the changes.
9. Close the Data Manager.

Example to Search for Strings that May or May Not Contain Apostrophes

1. Start a new project in Visual Basic. Form1 is created by default.
2. Below is the FORM1.FRM file saved in text. Bring this code into a text editor and save it as EXAMPLE.FRM. Then remove FORM1.FRM from your project, and add load EXAMPLE.FRM to the project. Then go to the Options menu and set Example as your start up form.

   VERSION 2.00
   Begin Form Example
      Caption         =   "Sample of searching for apostrophes in strings"
      ClientHeight    =   4020
      ClientLeft      =   1095
      ClientTop       =   1485
      ClientWidth     =   7365
      Height          =   4425
      Left            =   1035
      LinkTopic       =   "Form1"
      ScaleHeight     =   4020
      ScaleWidth      =   7365
      Top             =   1140
      Width           =   7485
      Begin CommandButton Command2
         Caption         =   "With Apostrophe"
         Height          =   375
         Left            =   4680
         TabIndex        =   4
         Top             =   720
         Width           =   2055
      End
      Begin CommandButton Command1
         Caption         =   "With out Apostrophe"
         Height          =   375
         Left            =   840
         TabIndex        =   1
         Top             =   720
         Width           =   2055
      End
      Begin ListBox List1
         Height          =   1005
         Left            =   2160
         TabIndex        =   0
         Top             =   2400
         Width           =   3375
      End
      Begin Data Data1
         Caption         =   "Data1"
         Connect         =   ""
         DatabaseName    =   "C:\VB3\BIBLIO.MDB"
         Exclusive       =   0   'False
         Height          =   495
         Left            =   5040
         Options         =   0
         ReadOnly        =   0   'False
         RecordSource    =   "Authors"
         Top             =   1440
         Visible         =   0   'False
         Width           =   2175
      End
      Begin Label Label4
         Caption         =   "<---- Samples ---->  Strings to search"
         Height          =   495
         Left            =   3000
         TabIndex        =   6
         Top             =   240
         Width           =   1575
      End
      Begin Label Label3
         Caption         =   "Atre', Shaku."
         Height          =   255
         Left            =   4800
         TabIndex        =   5
         Top             =   240
         Width           =   1935
      End
      Begin Label Label1
         Caption         =   "Brackett, Michael H."""
         Height          =   255
         Left            =   840
         TabIndex        =   3
         Top             =   240
         Width           =   1935
      End
      Begin Label Label2
         Caption         =   "Results, below"
         Height          =   255
         Left            =   3240
         TabIndex        =   2
         Top             =   1920
         Width           =   1335
      End
   End
   
     Dim pos%   'position of where an apostraphe may be in a string
   
     Sub check_apostrophe (var1 As String)
      pos% = InStr(1, var1, Chr$(39))
     End Sub
   
     Sub Command1_Click ()   'searches a string without apostrophe
       list1.Clear
       a$ = label1.Caption    'contains the string: Brackett, Michael H."
       pos% = 0
       Call check_apostrophe(a$) 'check for apostrophe in a string
       If pos% <> 0 Then
        tmp$ = Mid$(a$, 1, pos% - 1) & "*" 'strip out apostrophe for search
        criteria$ = "author like '" & tmp$ & "'"  'search with apostrophe
        data1.Recordset.FindFirst criteria$
        list1.AddItem data1.Recordset("author")
       Else
        criteria$ = "author = '" & label1.Caption & "'"
        data1.Recordset.FindFirst criteria$  'search without apostrophe
        list1.AddItem data1.Recordset("author")
       End If
     End Sub
   
     Sub Command2_Click ()   'searches a string with apostrophe
       list1.Clear
       a$ = label2.Caption    'contains the string: Atre', Shaku.
       pos% = 0
       Call check_apostrophe(a$) 'check for apostrophe in a string
       If pos% <> 0 Then
        tmp$ = Mid$(a$, 1, pos% - 1) & "*" 'strip out apostrophe for search
        criteria$ = "author like '" & tmp$ & "'"    'search with apostrophe
        data1.Recordset.FindFirst criteria$
        list1.AddItem data1.Recordset("author")
       Else
        criteria$ = "author = '" & label2.Caption & "'"
        data1.Recordset.FindFirst criteria$   'search without apostrophe
        list1.AddItem data1.Recordset("author")
       End If
     End Sub
   						

3. From the Run menu, choose Start (ALT, R, S), or press the F5 key to run the program. Click the Command1 button. Then click the Command2 button.