Apostrophe Causing Run Time Error'!

[Debased]

I have a form based on a table. Form has unbound combo box set to find record. It works fine
except for those names with an apostrophe like "Women's Center" (quotes mine)

I discovered this just by process of elimination. The error description didn't help a bit.
If I delete the apostrophe, it works fine.
Is there anyway to have names using apostrophe's and have it function properly?

Here's the error: Run-time error '3077': Syntax error (missing operator) in expression.


Thanks

 

[Kodo]

if you're passing text to a query that contains an apostrophe you must replace it.

mytext="Jim's car"

Mytext=replace(mytext,"'","''")

then pass the variable to the query.

 

[Debased]

Thanks for the reply. I am just using the following code that was generated by the wizard for the combo box. Not being advanced coder I am not sure where to place your reply code:

Private Sub Combo31_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Lastname] = '" & Me![Combo31] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

 

[Kodo]

Try

Set rs = Me.Recordset.Clone
Dim MyLastName as string

MyLastName=me.combo31
MyLastName=replace(MyLastName,"'","''")

rs.FindFirst "[Lastname] = '" & MyLastName & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

 

[Debased]

Thanks Kodo...I will try it this weekend...gotta be gone now. Have a great weekend!

 

[Pat Hartman]

It is NOT necessary to replace the apstrophe. You need to modify your code. Whenever a text field may contain an apostrophe, you MUST use quotes as the delimiter rather than apostrophes. Change your code such that any strings you create are delimited with quotes instead of apostrophes. An example would be:

rs.FindFirst "[Lastname] = " & chr(034) & MyLastName & chr(034)

Or define a global constant in a standard code module (this is what I do for clarity):

Public Const QUOTE As String = """"
...

rs.FindFirst "[Lastname] = " & QUOTE & MyLastName & QUOTE

 

[Kodo]

6 of one half dozen of the other.

 

[Pat Hartman]

I disagree Kodo. Did you actually test your code? Changing the value you search for to remove the apostrophe will get rid of the error. However, it won't find any matching record! The apostrophes are still IN the actual data. Changing the search criteria from 'Women's Center' to 'Women"s Center' may get rid of the error but in order to find any matching records, the search criteria needs to be - "Women's Center". That's why you need to change the DELIMITERS from single to double quotes.

 

[Kodo]

maybe I'm missing something here, but isn't he passing that to a query?

the replace function I use is a well known function throughout the programming community. I use it regularly in my ASP apps with out failure of any kind.

 

[Pat Hartman]

The Replace() function only works in newer versions of Access but that's not the problem. The code you suggested is changing the VALUE being searched for. How does that help? What needs to be changed is the character used to DELIMIT the value being searched for. Searching for Women"s Center will NOT find a record with the value Women's Center.

It doesn't matter where a string is used whether it is in code or SQL. If the value contained within the string may contain an apostrophe, the string MUST be delimited with quotes. It may not be delimited with aprostophes.

 

[Kodo]

The apostrophe is the escape for SQL.

You're not looking at the total parsing of SQL statement.

If I use the replace function on all ' inside the variable then SQL will see two apostrophes together and treat them as a single apostrophe . It also understands that this doesn't signify the end of the string.

 

[Pat Hartman]

Sorry, it looked like the code was replacing 1 apostrophe with 1 quote. I see now that it is replacing 1 apostrophe with 2 apostrophes. Replace() is available with A2K and newer.

 

[Kodo]

Sorry for the confusion Pat. I should have made it more clear.

 

[Debased]

Thanks for your help!

Just now getting back to work.. I used your suggestion Kodo and it works great! Thanks again!

 

[Kodo]

you're quite welcome