Run-time error '3077' when using an apostrophe in a name

Posted on 2002-07-19
Last Modified: 2008-03-10
In Access 97, I have a user who has a few names, such as O'Brien and O'Ryan.  When she types in the name field to do a search, as soon as she types in "o" and presses ENTER, she gets the following:

"Run-time error '3077', syntax error (missing operator) in expression".

Here's the line of code it's stopping on:

Me.RecordsetClone.FindFirst "[strWholeName]= '" & Me![Combo40] & "'"

Any ideas??????
Question by:esu4236
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
LVL 54

Accepted Solution

nico5038 earned 100 total points
ID: 7165969

Me.RecordsetClone.FindFirst "[strWholeName]= " & chr(34) & Me![Combo40] & chr(34)


Expert Comment

ID: 7166917
You need to create a function to replace the apos

such as the following

Function replaceApos(strToChk As String) As String
Dim X As Integer
Dim tmpstring As String
    For X = 1 To Len(strToChk)
    If Mid(strToChk, X, 1) <> "'" Then
        tmpstring = tmpstring & Mid(strToChk, X, 1)
        tmpstring = tmpstring & Mid(strToChk, X, 1) & "'"
    End If

replaceApos = tmpstring
End Function

Then your code should read
Me.RecordsetClone.FindFirst "[strWholeName]= '" & replaceApos(Me![Combo40]) & "'"

This will double up the apostrophe's, you could enhance the function to include the item you are looking for such as (") which could return the corrected string

Author Comment

ID: 7172236

Your solution worked great!!!!!!!!!!!   Thanks a bunch. Have a GREAT day.  
LVL 54

Expert Comment

ID: 7172691
Glad I could help.

Success with your application.


Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

739 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