• Book titles containing apostrophe cause errors

    Home » Forums » AskWoody support » Productivity software by function » MS Access and database help » Book titles containing apostrophe cause errors

    Author
    Topic
    #491492

    I have a library program for books. Some book titles have an apostrophe in them, example: Christy’s Choice.
    One lookup in a form uses a ComboBox to list the titles. Whenever a title with apostrophe is selected in the combo box list,
    an error box comes up “Runtime-error 3077 Syntax error (missing operator) in expression.

    Debug displays with the rs.FindFirst line highlighted yellow:

    Code:
    Private Sub Combo63_AfterUpdate()
        ' Find the record that matches the control.
        Dim rs As Object
    
        Set rs = Me.Recordset.Clone
        rs.FindFirst "[title] = '" & Me![Combo63] & "'"
        If Not rs.EOF Then Me.Bookmark = rs.Bookmark
    End Sub
    

    All other title selections work correctly and display the book details in the form.
    Is there an addition to rs.FindFirst line to accept the apostrophe?

    Viewing 2 reply threads
    Author
    Replies
    • #1417281

      This is a common problem when searching for surnames which contain an apostrophe, such as O’Malley. A fairly complex solution involves checking for the existance of the apostrophe character in the search string using the INSTR() function, and then substituting the wildcard character “?” for that character. You might also choose to just truncate the search string – though that is likely to cause false positives if you have a fairly large table. You might also consider changing the position of the single and double quotes – although that can cause problems with titles that contain the double quote. Hopefully that gives you some ideas to try.

    • #1417294

      Hi Pillarcas,

      Try this modification:

      [Code]
      Option Compare Database
      Option Explicit

      Private Sub Combo63_AfterUpdate()
      ‘–Find the record that matches the control.
      Dim rs As Object
      Dim strSearch As String

      If InStr(1, Me.Combo63, “‘”) > 0 Then ‘ An apostrophe was found in the data
      strSearch = Chr(34) & Me.Combo63 & Chr(34)
      Else
      strSearch = “‘” & Me.Combo63 & “‘”
      End If

      Debug.Print “[CompanyName] = ” & strSearch

      Set rs = Me.Recordset.Clone

      rs.FindFirst “[CompanyName] = ” & strSearch
      If Not rs.EOF Then Me.Bookmark = rs.Bookmark
      End Sub
      [/Code]

      This is based on a Microsoft KB article 286242:

      ACC2002: Unable to Use FindFirst to Retrieve Value with Apostrophe
      http://support.microsoft.com/?id=286242

      A better solution, of course, is to provide a primary key (either autonumber or text that does not include special characters) as a hidden column in the combo box row source, and base the search on this key field. For example, if you use the wizard in Access 2003, with a form based on the Customers table, the resulting combo box row source will be this:

      SELECT [Customers].[CustomerID], [Customers].[CompanyName] FROM [Customers];

      Note: I would add an ORDER BY clause as well:
      SELECT [Customers].[CustomerID], [Customers].[CompanyName] FROM [Customers] ORDER BY Customers

      or, more simply:
      SELECT CustomerID, CompanyName FROM Customers ORDER BY Customers

      The combo box has 2 columns, with column widths of 0″;1″ and with bound column = 1
      This means that the first column in the SELECT statement, CustomerID, will not be displayed to the user, since the column width is zero. However, it will be used in the AfterUpdate Event procedure, since it is the bound column:

      Private Sub Combo63_AfterUpdate()
      ‘ Find the record that matches the control.
      Dim rs As Object

      Set rs = Me.Recordset.Clone
      rs.FindFirst “[CustomerID] = ‘” & Me![Combo63] & “‘”
      If Not rs.EOF Then Me.Bookmark = rs.Bookmark
      End Sub

      This version, which uses the 5 character text primary key in the Northwind Customers table, will not choke with customer names that include special characters.

      Notes:
      1) Now would be a good time to rename Combo63 to something more logical, such as cboFindCustomer.
      2) Disregard the “ACC2002” in the title, as the same logic applies to all versions of Access
      3) The KB author could have done a much better job, instead of just showing a hard-coded solution for a single customer!

      • #1417500

        Thanks, the simpler fix was to use the primary key field and hide it in the combo box. Works fine

    • #1418917

      Finding by Primary Key is definitely the best solution for this situation (and most others), however for what it’s worth, sometimes a search/criteria needs to be taken from text which could possibly contain the apostrophe.

      Using the initial example it would be possible to make this simple change:

      Private Sub Combo63_AfterUpdate()
      ‘ Find the record that matches the control.
      Dim rs As Object

      Set rs = Me.Recordset.Clone
      rs.FindFirst “[title] = ” & Chr(34) & Me![Combo63] & Chr(34)
      If Not rs.EOF Then Me.Bookmark = rs.Bookmark
      End Sub

      This replaces the apostrophe used by the criteria string ([title]=’War of the Worlds’) with the quote mark ([title]=”War of the Worlds”) thus eliminating possible conflicts when the text, in this case the book title, also contains an apostrophe.

    Viewing 2 reply threads
    Reply To: Book titles containing apostrophe cause errors

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: