Book titles containing apostrophe cause errors

Topic

New Reply

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?

Reply | Quote

Replies

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.

Reply | Quote

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!

Reply | Quote

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

Reply | Quote

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.

Reply | Quote