[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1066
  • Last Modified:

MS Access 2003 - Combo box Search fails when there is a apostrophe in the Search field

I have a form that uses the code listed below on the after update of a combo box to search for the selected company name. However, if the company name contains an apostrophe (Example: Dave's Trucking,  George's Service Center) the seasrch fails and generates a VBA Error statement Run-Time Error 3077, Syntax error (missing operator )in expression.

Any suggestions as to how to get around this problem other than removing the apostrophe out of each name (not practical).

After Update event on company (combo25) search field.
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[COMPANY] = '" & Me![Combo25] & "'"
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Any suggestions on how to handle this problem.

Lou Dufresne
Lou Dufresne
3 Solutions
Leigh PurvisDatabase DeveloperCommented:
You could try

rs.FindFirst "[COMPANY] = '" & Replace(Me![Combo25],"'","''") & "'"
Rey Obrero (Capricorn1)Commented:

 rs.FindFirst "[COMPANY] = " & chr(34) & Me![Combo25] & chr(34) & ""
Leigh PurvisDatabase DeveloperCommented:

rs.FindFirst "[COMPANY] = """ & Me![Combo25] & """"
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Leigh PurvisDatabase DeveloperCommented:
< :-S >
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Actually, every time you manipulate strings in VBA, or import/export, or a whole lot of other stuff, it will fail if there are apostrophe marks in the string.
My record search routines simplify the process,  I allow the user to type a minimum of 3 characters into a text control. Once the minimum number of characters have been entered I make a Search Button Appear onthe form.

The search populates a subform with all records that START with those characters.  You could type in DAV and it would retrieve Daves tree...  Dave's tree...   Davis Food ....   and then use the subform to select the desired record.

Works fast and clients like it because they don't even need to remember the whole name.

You could also incorporate a SOUNDEX function (many samples can be found)


Has complete source code and explains the usage

It Ingnores Case and punctuation

Drawback it might make multiple matches on initial call... IE:   Smith and Smyth may be concidered a match.  If you are dealing with longer names it should be less of a concern.

What I would do at that point is use the SOUNDEX function to filter my total list.  If only one potential match is found it selects that record.  If more than one match exists, populate a small grid subform (as described above) and make the final selection.  

Of you could do a find first, find next.

You would store the SOUNDEX code for each name in a seperate field in the table, and do the Soundex of the name you are searching for and match Soundex codes.

To speed and simplify the process you could do a Soundex only on the first X number of characters  allow the user to enter the same X number of characters in a search text box.

Lou DufresneIT Business Analyst CSM / Project ManagerAuthor Commented:
Thanks everyone for the great ideas


Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now