Microsoft KB Archive/113955

= How to Use FindFirst with Strings that Have Apostrophe in VB =

Article ID: 113955

Article Last Modified on 1/8/2003

-

APPLIES TO


 * Microsoft Visual Basic 3.0 Professional Edition

-



This article was previously published under Q113955



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.



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: <pre class="fixed_text">  Brackett, Michael H.                        </li>  Place a double quotation mark at the end of this field, so it looks like this: <pre class="fixed_text">  Brackett, Michael H."                        </li> Use the Data control to select the next record, and choose Yes to commit the changes.</li> Close the Data Manager.</li></ol>

Example to Search for Strings that May or May Not Contain Apostrophes
<ol> Start a new project in Visual Basic. Form1 is created by default.</li>  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 </li> 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.</li></ol>

Additional query words: 3.00

Keywords: KB113955

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.