Cannot interpret token "" at position 14

neo37
neo37 used Ask the Experts™
on
Hello.  I am still a bit unfamiliar with accessing databases the way I am doing here...so I'm probably missing something obvious...but here's the problem:

This line of code works fine:
            foundRows = testds.Tables(0).Select("(Control = '" & iControl & "') AND KeywordHits = '" & sKeyword & _
                "' AND KeywordList = '" & sKeywordList & "' AND FieldUpdated = '" & sFieldUpdated & "'")

Except when sKeyword is a string that contains a single apostrophe mark.  Example:  sKeyword = "abc w/2 d'ef"

So I tried changing that part of the Select string to use double-quotes around sKeyword instead of single-quotes.  Like this:
            foundRows = testds.Tables(0).Select("(Control = '" & iControl & "') AND KeywordHits = " & chr(34) & sKeyword & _
                chr(34) & " AND KeywordList = '" & sKeywordList & "' AND FieldUpdated = '" & sFieldUpdated & "'")

That didn't work.  I got a "Syntax error: Missing operand after 'w' operator."  I'm assuming this is referring to the w in the sKeyword variable, which is immediately followed by a /.  I don't know if the / is causing problems, or what.

So then I tried various things similar to this:
            foundRows = testds.Tables(0).Select("([Control] = " & Chr(34) & iControl & Chr(34) & " AND [KeywordHits] = " & _
                Chr(34) & sKeyword & Chr(34) & " AND [KeywordList] = " & Chr(34) & sKeywordList & Chr(34) & _
                " AND [FieldUpdated] = " & Chr(34) & sFieldUpdated & Chr(34) & ")")

And I now get this error message:
Cannot interpret token '"' at position 14.  (That's a single quote, double quote, single quote.)

I'm wondering now if double-quotes can't be used anywhere in the select statement.  If that's not the problem, why am I getting this error?  If that is the problem, how do I pass the string that includes a single quote if I'm using single quotes to surround the values I'm passing?

Thanks in advance for your help!!!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
inside a string literal, when you need an apostrophe, try doing two apostrophes one after the other like this: ''

this is not the same as a double quote mark, like this: "

try this technique in your first example.

Also, you can use:
   sKeyword = Replace(sKeyword, "'", "''")     <--  replaces any single apostrophes with two apostrophes.
to douuble-up all the embedded single quotes before setting up your "Select" command.

Try that

AdsB
Most Valuable Expert 2012
Top Expert 2008

Commented:
Are you still having a problem with this, or is it solved?

Author

Commented:
Thanks for your help AdsB!  That worked beautifully.  Sorry it took me so long to accept the answer.  My schedule's been really hectic lately, and after I tried your suggestion I completely forgot about coming back here.  I just now remembered when I got the e-mail notification that another comment had been posted.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial