Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Feb 2006
    Posts
    71
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Book titles containing apostrophe cause errors

    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?
    Last edited by RetiredGeek; 2013-10-17 at 18:42. Reason: Added code tags

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,481
    Thanks
    3
    Thanked 41 Times in 41 Posts
    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.
    Wendell

  4. The Following User Says Thank You to WendellB For This Useful Post:

    pillarcas (2013-10-18)

  5. #3
    Star Lounger tgw7078's Avatar
    Join Date
    Jul 2010
    Location
    Seattle, WA., USA
    Posts
    82
    Thanks
    1
    Thanked 10 Times in 10 Posts
    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

    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!
    Last edited by tgw7078; 2013-10-18 at 01:36. Reason: Added MSKB reference and additional details
    Tom Wickerath
    Microsoft Access MVP
    4/1/2006 - 3/31/2012

  6. #4
    Star Lounger
    Join Date
    Feb 2006
    Posts
    71
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks, the simpler fix was to use the primary key field and hide it in the combo box. Works fine

  7. #5
    New Lounger
    Join Date
    Jan 2010
    Location
    Melbourne, Vic, Australia
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •