asked on

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

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

ASKER CERTIFIED SOLUTION

nico5038

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

kiddiec

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)
Else
tmpstring = tmpstring & Mid(strToChk, X, 1) & "'"
End If
Next

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

esu4236

ASKER

nico5038,

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

nico5038

Glad I could help.

Success with your application.

Nic;o)