No one answered this properly....!!!

VB Prompts error msg when I use FindFirst or FindLast
to search a string which has puncutation characters viz., ' (single quote) or comma (,).... For Eg. "Tom's Inn"

myrs.Findfirst "Name = '" & tmpName & "'"

where tmpName is "Tom's Inn".


tmpName is a string Variable
Name is the fieldName in my Recordset MyRs

Pls try this & let me know.

Don't suggest me to remove those puncutation characters...
It is not possible to remove from my customers data.

Pls help me

Mohan Kumar VS.

hai,
generally if you want to find the first occurance of any string in a bigger one instr() used in vb, as you asked take a temporary string for which you want to go for search.
here i am sending code in vb and it will work.

Private Sub Command1_Click()
Dim source As String
Dim temp As String
Dim f As Integer
source = ""
source = tt.Text
temp = "sag 'inn"
MsgBox temp
f = InStr(source, temp)
MsgBox f
End Sub

you enter a large text in text box tt and put sag'inn some where in the string.

The character ' is a reserved keycharacter in SQL, and it indicates to the parser, that the following text (until next ') should be read as a string.

The way to overcome is to run your searchdata through this function, and then use this in you Findfirst.

Code:

---

Function SearchReplace(byval Text as string) as string
dim intPos as integer
dim strTemp as string

intpos = instr(text, "'")

do while intpos
  strTemp = strTemp & left$(text, intPos-1)
  strTemp = strTemp & "''"
  strTemp = strTemp & mid$(text, intpos+1)
 
  intpos = instr(intPos+1m text, "'")
loop

SearchReplace = strTemp
end function

---

Code:

---

myrs.Findfirst "Name = '" & SeacrhReplace(tmpName) & "'"

---

I've tried it, and it works.

The follwing code is just a test form containing a ADO Data control (Adodc1), which opens a temp database. The temp database contaions the following information: "O'R", "Me", "Test".
When I test the code .Find returns "O'R".

Code:

---

Private Sub Form_Load()

Me.Adodc1.Recordset.Find "Test = '" & SearchReplace("O'R") & "' "
If Me.Adodc1.Recordset.EOF Then Debug.Print "Nothing found"

End Sub

Function SearchReplace(ByVal Text As String) As String
Dim intPos As Integer
Dim strTemp As String

intPos = InStr(1, Text, "'")

Do While intPos
  strTemp = strTemp & Left$(Text, intPos - 1)
  strTemp = strTemp & "''"
  strTemp = strTemp & Mid$(Text, intPos + 1)
 
  intPos = InStr(intPos + 1, Text, "'")
Loop

SearchReplace = strTemp
End Function

---