Find Method and Single Quotes

[harjeen]

I'm sure this a problem that a lot of you have seen before. When trying to use the Find method on a string that contains an apostrophe, an error occurs.

For example:

rcset_master.Find "String = '" & txtBox.text & "'"

where txtbox.text = "Bob's Sporting Goods"

I've searched through the VBWorld Q and A, and found a fix for this problem, which involves replacing all single quotes in the search string with two single quotes. ie:

rcset_master.Find "String = '" & replace(txtbox.text, "'", "''") & "'"

This solution works fine for strings that contain only ONE apostrophe to start with. However, it does not work for a string with more than one to start, like:

"Please finish setting the printer's options on the printer's panel before continuing."

Has anyone found a workaround for this problem? Is there a MS KB article?

TIA,

Nahid Harjee

[wick77]

Here is a function someone gave me to replace all the apostrophes in all textboxes with two apostrophes.

code:

Public Sub FixFormQuotes(glCallfrm As Form)
Dim mycontrol As Control
For Each mycontrol In glCallfrm.Controls
If TypeOf mycontrol Is TextBox Then
mycontrol.Text = FixQuotes(mycontrol.Text)
End If
Next

End Sub
Public Function FixQuotes(A As String)

Dim i As Integer
i = InStr(A, "'")

Do While i > 0
A = Left(A, i) & Mid(A, i)
i = InStr(i + 2, A, "'")
Loop

FixQuotes = A

End Function

Hope it helps.

[harjeen]

Thanks Wick,

But the problem isn't with replacing the single quotes. I can do that. The problem is that this work around is only for strings that original contain ONE apostrophe. If the original string contains more than apostrophe, the work around fails.

[wick77]

Maybe i misunderstood but the code I gave you replaces all apostrophes even if there is multiple in a string.

This is what I use for multiple apostrophes for FindFirst, maybe your situation is different.

[Clunietp]

the VB6 Replace function also replaces ALL instances of a given string -- you must be implementing it incorrectly

[harjeen]

I think I'm confusing everybody. Replacing the single quote's is fine. I CAN do that with the replace function. ie:

Replace("Bob's and Nina's Diner", "'", "''")

which returns "Bob''s and Nina''s Diner".

That's not the problem.

The problem is with the find method. It only works for a string, returned from the replace function, that originally had ONLY ONE apostrophe. If the string had more than one apostrophe originally, all of them are replaced (as they should be) by the replace function, but the find method causes an error.

One solution I just found is to delimit the string with # signs. ie:

rcset_master.Find "String = #" & txtbox.text & "#"

This works fine for all strings with apostrophes (no matter how many there are), but obviously does not work for a string containing a # sign.

So what I need, if possible, is a method of deliminating a string, so that the find method can parse it, regardless of whether it contains # or apostrophes. An example string would be:

"Rob's and Ed's Diner on Highway #10"