ACC2: Cannot Search for Name with Apostrophe in Find Customers

• Microsoft Access 2.0

SYMPTOMS

Moderate: Requires basic macro, coding, and interoperability skills.

When you are using the Find Customers form in the sample database SOLUTION.MDB, you cannot search for a customer whose name contains an apostrophe ('). For example, if you try to search for a customer with the name "B's Foods," you receive the following error message:

Syntax error in query expression '[Company Name] Like 'B's Foods*''

RESOLUTION

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 "Building Applications" manual.

The following steps demonstrate how to modify the AddToWhere Sub procedure so that you can search for customer names containing apostrophes.

NOTE: In the following sample code, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this code in Access Basic.

1. Quit Microsoft Access if it is running, and copy the SOLUTION.MDB file to a file called MYSOLUT.MDB.



2. Start Microsoft Access and open the MYSOLUT.MDB database.



3. In the Select A Category Of Examples box, select Sample Forms. In the Select An Example box, select Find Customers. Choose OK.



4. View the form in Design view. From the View menu, choose Code.



5. In the Procedure box on the toolbar, select AddToWhere, and place an apostrophe (') at the beginning of the line that reads:
   

   
         Mycriteria = (Mycriteria & FieldName & " Like " & Chr(39) & _
         Fieldvalue & Chr(42) & Chr(39)) 

   
   Placing an apostrophe at the beginning of the line comments the line out.



6. Enter the following code just below the line that you commented out in step 5:
   

   
         If InStr(1, fieldvalue, "'") Then
           Mycriteria = (Mycriteria & FieldName & " Like " & Chr(39) &
           Left(Fieldvalue, InStr(1, Fieldvalue, "'") - 1) & "'" & _
           Mid(Fieldvalue, InStr(1, Fieldvalue, "'")) & Chr(42) & Chr(39))
         Else
           Mycriteria = (Mycriteria & FieldName & " Like " & Chr(39) & _
           Fieldvalue & Chr(42) & Chr(39))
         End If 




7. Close the module and then view the form in Form view.



8. Search for a company whose name contains an apostrophe.



9. From the File menu, choose Save Form.

Keywords : kbusage FmsEvnt
Version : 2.0
Platform : WINDOWS
Issue type : kbprb