How to Use FindFirst with Strings that Have Apostrophe in VB

ID: Q113955

The information in this article applies to:

Microsoft Visual Basic programming system for Windows, version 3.0

SUMMARY

This article shows by example how to search for a string that may contain an embedded apostrophe ('). You need to use a slightly different type of FindFirst method to search for strings that may contain apostrophes. This is noted in the Microsoft Access Help file. There are other alternative ways to search for strings that contain an apostrophe in them, this article gives two examples.

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:

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.

Select the Authors table, and choose the Open button.

From the Open window, use the Data control at the bottom to move to the record with the Author field is:
```
   Atre, Shaku 
```

Enter a apostrophe after the letter e so it looks like this:
```
   Atre', Shaku 
```

Use the Data control to select the next record, and choose Yes to commit the changes.

Move two more records to the record where the Author field is:
```
   Brackett, Michael H. 
```

Place a double quotation mark at the end of this field, so it looks like this:
```
   Brackett, Michael H." 
```

Use the Data control to select the next record, and choose Yes to commit the changes.

Close the Data Manager.

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

Start a new project in Visual Basic. Form1 is created by default.

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

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.

Additional query words: 3.00

Keywords :
Version :
Platform :
Issue type :