Apostrophe

[gtroiano]

ok, i have read tons of posts about the "apostrophe problem" but to no avail, they do not seem to help me.
i have a form with a text box that searches a table for scholarship account titles. the field is called AccountTitle. then i have a "Find Next" button that, well, finds the next occurence of the search string, in this case, the account title. the problemo is that some account titles have apostrophes in them and i receive "Run-time error '3077': Syntax error (missing operator) in the expression".

This is the code behind the text box:

Private Sub txtFind_AfterUpdate()
On Error GoTo Err_txtFind_AfterUpdate

Dim stResponse As String

stResponse = txtFind
If stResponse = "" Then
GoTo Proc_Exit
Else
stFindCriteria = "[AccountTitle] Like '*"
stFindCriteria = stFindCriteria & stResponse & "*'"
End If

Me.fsubMaintainAccounts.Form.RecordsetClone.FindFirst stFindCriteria
If Me.fsubMaintainAccounts.Form.RecordsetClone.NoMatch Then
MsgBox "Search text was not found."
stFindCriteria = ""
Else
Me.fsubMaintainAccounts.SetFocus
Me.fsubMaintainAccounts.Form.Bookmark = Me.fsubMaintainAccounts.Form.RecordsetClone.Bookmark
End If

Err_txtFind_AfterUpdate:
Exit Sub

Proc_Exit:
Exit Sub

End Sub

This is the code behind the "Find Next" button:

Private Sub cmdFindNext_Click()

If stFindCriteria = "" Then
GoTo Proc_Exit
Else

Me.fsubMaintainAccounts.Form.RecordsetClone.FindNext stFindCriteria
If Me.fsubMaintainAccounts.Form.RecordsetClone.NoMatch Then
MsgBox "Search text was not found."
stFindCriteria = ""
Else
Me.fsubMaintainAccounts.SetFocus
Me.fsubMaintainAccounts.Form.Bookmark = Me.fsubMaintainAccounts.Form.RecordsetClone.Bookmark
End If
End If

Proc_Exit:
Exit Sub

End Sub

For example, if i type "Int'l Assoc. Scholarship" in the text box nothing happens, i don't even receive an error. but when i click the "Find Next" button, that's when i get the aformentioned error.
if someone could be so kind as to check this out and pass on any help, i would be greatly aprreciative.

post script:
i would like not to have to replace the apostrophe with a quotation or some other character.

 

[smedvid]

I've encountered this beast on many occassions. In fact, I am working on it now with a Auto-Network Mdb Compact routine. My work arounds have been to create an addtional field to store corrected name field that removes the offending characters and also to use the double single quote approach. Yet another approach, I've seen is to use the ASC() number for a single quote, although I never tried that method.

For Example... Instead of trying to locacte "Int'l Studies" try "Int''l Studies". Type in this example via hard-coding to see if it works.

Here is some psuedo code to replace a string. You can try to modify the code to replace a single quote (') with two single quotes ('').

Function Replace_String(ByVal lcstring1 As String, lcsearch As String, lcreplace As String) As String
'This function is designed to change any single variable occurrance to another variable
'based on parameters passed in. For the current problem, a quote appears to be the problem.
If lcsearch <> lcreplace Then
'*loop until there are no more search items found
While InStr(1, lcstring1, lcsearch) <> 0
'*strip the search item working from left to right
'*by finding the item and concatenating the left
'*and right portions except the search value
lcstring1 = Left(lcstring1, InStr(1, lcstring1, lcsearch) - 1) & replace _
& Right(lcstring1, Len(lcstring1) - ((InStr(1, lcstring1, lcsearch) - 1) + Len(lcsearch)))
Wend
'*return the stripped value
Replace_String = lcstring1
End If
End Function

htwh,


Steve Medvid
&quot;IT Consultant & Web Master&quot;

http://users.erols.com/medvid/

 

[galar]

Hi,

Another solution which i tried successfully.
You can catch the error when constracting the search statement and redirect to line when you build the same SQL statement but instead of ' you will use &quot;.

for example:
&quot;whatever Like &quot;&quot;*&quot; & parameter & &quot;*&quot;&quot;&quot;