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
  • 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

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…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

911 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

20 Experts available now in Live!

Get 1:1 Help Now