' in a query?

[George Hester]

I have this SQL in a query:

sSql = "SELECT *" & vbCrLf & _
"FROM tblTest4" & vbCrLf & _
"WHERE tblTest4.Caption_Name LIKE '%" & qryAuthor & "%';"

But I would like to do a query on the character ' But as you can see when
qryAuthor = ' I have a problem. If I try one ' then the query hangs. If I
use '' I get an error . I have tried Chr(39) and ' none of which work.
Any ideas how to query on this character? Thanks.

 

[Rob Parker]

Hi George,

Replace the ' delimiters with "" (that's two double quote characters) in the
last line:
"WHERE tblTest4.Caption_Name LIKE ""%" & qryAuthor & "%"";"

HTH,

Rob

 

[George Hester]

Yes I have tried that. The errors I am getting are two. First this one:

Closing delimiter not found for the string beginning at postition 831 in the
command. The string begins with: ')} AS [tblTest4].

The second is this error:

Err.Source =
Err.Number = 80040E14
Err.Description =

The two always come together. The second is my attempt at error handling.

Or it finds nothing or it hangs. There are ' for the field in the database
so not sure what more to try.

 

[Rob Parker]

Sorry George,

I don't understand either of those errors. The first, referring to position
831, leads me to suspect that the problem might lie with the contents of
qryAuthor, which you are inserting into your sSql string. I suggest you try
some debug.print statements to find out exactly what the various strings are
evaluating to, and perhaps try cutting/pasting those strings from the
immediate window into a query and running it, to see what happens then.

Other than that, there's nothing else I can think of at the moment.

Rob

Yes I have tried that. The errors I am getting are two. First this one:

Closing delimiter not found for the string beginning at postition 831 in
the
command. The string begins with: ')} AS [tblTest4].

The second is this error:

Err.Source =
Err.Number = 80040E14
Err.Description =

The two always come together. The second is my attempt at error handling.

Or it finds nothing or it hangs. There are ' for the field in the database
so not sure what more to try.

--

George Hester
_________________________________
message

Hi George,

Replace the ' delimiters with "" (that's two double quote characters) in the
last line:
"WHERE tblTest4.Caption_Name LIKE ""%" & qryAuthor & "%"";"

HTH,

Rob

 

[John W. Vinson]

I have this SQL in a query:

sSql = "SELECT *" & vbCrLf & _
"FROM tblTest4" & vbCrLf & _
"WHERE tblTest4.Caption_Name LIKE '%" & qryAuthor & "%';"

But I would like to do a query on the character ' But as you can see when
qryAuthor = ' I have a problem. If I try one ' then the query hangs. If I
use '' I get an error . I have tried Chr(39) and ' none of which work.
Any ideas how to query on this character? Thanks.

Try replacing the ' in the query string with two consecutive ' marks:

"WHERE tblTest4.Caption_Name LIKE '%" & Replace(qryAuthor, "'". "''") &
"%';"

For clarity (don't do it this way!!) that's replacing " ' " with " ' ' ". The
latter will be treated as just a single singlequote.

John W. Vinson [MVP]

 

[George Hester]

Hi Rob. I figured it out finally. It turns out Find also exhibits this
issue. I thought it only came up in my definition of sSQL used in the query.
But the issue will also arise in the Find command used in my DAP. So I had
to just do something like this:

qryAuthor = Replace(qryAuthor,"'","''")

and that fixed it. I was spending all my time looking at the sSQL thinking
that was where the issue was. It was but I didn't realize that the
single-quote issue also comes up in the Find I was using. Wll that one's
done. Thanks for your help.

--

George Hester
_________________________________

Sorry George,

I don't understand either of those errors. The first, referring to position
831, leads me to suspect that the problem might lie with the contents of
qryAuthor, which you are inserting into your sSql string. I suggest you try
some debug.print statements to find out exactly what the various strings are
evaluating to, and perhaps try cutting/pasting those strings from the
immediate window into a query and running it, to see what happens then.

Other than that, there's nothing else I can think of at the moment.

Rob

Yes I have tried that. The errors I am getting are two. First this one:

Closing delimiter not found for the string beginning at postition 831 in
the
command. The string begins with: ')} AS [tblTest4].

The second is this error:

Err.Source =
Err.Number = 80040E14
Err.Description =

The two always come together. The second is my attempt at error handling.

Or it finds nothing or it hangs. There are ' for the field in the database
so not sure what more to try.

--

George Hester
_________________________________
message

Hi George,

Replace the ' delimiters with "" (that's two double quote characters)

in
the

last line:
"WHERE tblTest4.Caption_Name LIKE ""%" & qryAuthor & "%"";"

HTH,

Rob

I have this SQL in a query:

sSql = "SELECT *" & vbCrLf & _
"FROM tblTest4" & vbCrLf & _
"WHERE tblTest4.Caption_Name LIKE '%" & qryAuthor & "%';"

But I would like to do a query on the character ' But as you can see when
qryAuthor = ' I have a problem. If I try one ' then the query hangs.

If
I

use '' I get an error . I have tried Chr(39) and ' none of which work.
Any ideas how to query on this character? Thanks.

 

[George Hester]

Yes John that was the easiest way of fixing it. I just had to do it in two
places instead of one. Since I used ' in various places in the Database I
didn't realize that the issue also comes up here:

MSODSC.RecordsetDefs.Item(0).ServerFilter = myFilter

Where myFilter contains database entries with '.