ACC2: Cannot Search for Name with Apostrophe in Find Customers

 Last reviewed: July 16, 1997
Article ID: Q121260
The information in this article applies to:
  • Microsoft Access version 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            : FmsEvnt kbusage

Version             : 2.0

Platform            : WINDOWS

Hardware            : X86

Issue type          : kbprb

Resolution Type     : kbcode








THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS
PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND.  MICROSOFT DISCLAIMS
ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES
OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.  IN NO
EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR
ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL,
CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF
MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE
POSSIBILITY OF SUCH DAMAGES.  SOME STATES DO NOT ALLOW THE EXCLUSION
OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES
SO THE FOREGOING LIMITATION MAY NOT APPLY.






	
	Last reviewed:  July 16, 1997
	

	© 1998 Microsoft Corporation. All rights reserved. Terms of Use.