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

Posted on 2006-05-05
Last Modified: 2010-07-27
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.

Question by:Lou Dufresne
    LVL 44

    Expert Comment

    by:Leigh Purvis
    You could try

    rs.FindFirst "[COMPANY] = '" & Replace(Me![Combo25],"'","''") & "'"
    LVL 119

    Assisted Solution

    by:Rey Obrero

     rs.FindFirst "[COMPANY] = " & chr(34) & Me![Combo25] & chr(34) & ""
    LVL 44

    Accepted Solution


    rs.FindFirst "[COMPANY] = """ & Me![Combo25] & """"
    LVL 44

    Expert Comment

    by:Leigh Purvis
    < :-S >
    LVL 65

    Expert Comment

    by:Jim Horn
    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.
    LVL 11

    Assisted Solution

    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.

    LVL 1

    Author Comment

    by:Lou Dufresne
    Thanks everyone for the great ideas


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
    I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
    With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now