Single quote in search string.

If I want do search for a single quote, is the only option to double the single quote?

In other words is constructing the SQL statement like this the only way to handle the problem?:
strTarget = "'I can''t go on!"
strSQL = "SELECT * FROM MY_TABLE WHERE MYFIELD = ' " & strTarget & "'"

         (I'm using Access to construct strSQL, and the last is double-single-double quotes.)

(I'm using doublequotes to delineate the string, since I'm working in Access, using ADO against an Oracle backend.)

It could get messy if there are TWO or MORE single quotes in the search item.

It'd be nice if we could have the option of defining a '' (two single quotes) as the delimiter, similarly to the way it's done in Access (where double quotes are used as the delimiter. There, the string would look like: strTarget = "'I can't go on!"
strSQL = "SELECT * FROM MY_TABLE WHERE MYFIELD = """ & strTarget & """"

        (In the last line, all quotes are double-quotes.) This last looks worse, but it's actually better, since I don't have to 'customize' the target string. Received on Mon May 01 2000 - 00:00:00 CDT