ADO Filter

select from {table} where X='test''1111'

You have to double up on 's that appear as literals.

M

I know, but if I have more then one quotes then i does not work.

PS

I'm working with a local Recordset with the Filter mehtod.

Make sure that record set is of type dynaset and then execute the follwing loop.

rs.MoveFirst
    Do Until rs("fieldname") = var
        rs.Move (1)
            If rs.EOF = True Then
                               
            Exit Do
            Exit Sub
            End If
    Loop

This is not the solution...

Thanks any way

Omri

Use this:
    rs.Filter "x = '" & Replace("test'1111", "'", "''") & "'"

emoreau

This is very true, if you have only one quotes in your string, and does not work if you have more :)

Do you have a function that is called Replace?

Here is what I get when running it from the Immediate Window.
? replace("1'2'3","'","''")
1''2''3

Maybe you can try to use VBA.Replace to be sure to use this function.

The problem is not the replace but the Filter method in ADO , it returns an error for this kind of string

Could you paste here your Filter line so that we can see!

You have to double up on 's in your string. If you have two, you need FOUR, etc.

M

Use this:
 rs.Filter="X="+Chr(34)+"test"+Chr(39)+"11111"+Chr(39)+Chr(34)

Code a function like this:

Public Function UnQuote(txt as String) as String
'
if len(txt) < 1 then exit function
'
dim work as string
dim achar as string
dim ndx as integer
'
work = ""
for ndx = 1 to len(txt)
  achar = mid(txt, ndx, 1)
  if achar = "'" then achar = achar & "'"
  work = work & achar
next ndx
'
UnQuote = work
'
End Function

Use this when building your SQL:

sql = "select from {table} where X='" & _
       unquote("test''1111") & _
       "'"

M

10Points

The problem is not VB syntax but ADO, ADO returns an error if you try to filter a string that has more then one quotes.

This is an ADO question!!!

Code given works for me.

M

what version of ado are you using?

emoreau

First of all I'm glade that someone got the problem.
Your answer, I already know, I thought maybe this was not a
Microsoft bug. I guess I underestimated myself.

I will give the points for the effort :)