[AccessD] Building SQL statement

Replace(MyText,Chr(39),Chr(39)&Chr(39))
Should do it.
Arthur

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of John W. Colby
Sent: August 24, 2005 11:23 PM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] Building SQL statement

I'm attempting to build a SQL statement to append data into a record.  One
of the fields is text, including (possibly) any valid text including ' - for
example in contractions such as "aren't" etc.

My SQL statement is building up a statement in the format:

            lstrSQL = "INSERT INTO WebNewsItem ( WebNewsSourceID, Link,
Title, ArticleDescription ) " & _
                        "SELECT " & webNewsSourceID & " AS WebNewsSourceID,
'" & link & "' AS Link, '" & title & "' AS Title, '" & description & "' AS
ArticleDescription;"

The problem is that description can contain the ' character and thus I end
up with an invalid SQL statement since the ' embedded in description "looks
like" the ' that is supposed to enclose the text.

I can't for the life of me remember how I am supposed to handle this.

I could punt and open a recordset, create a new record, and place the data
directly into the field, which I may end up doing but I sure would prefer to
just do the "append query" thing.  It is (was) much simpler.

John W. Colby
www.ColbyConsulting.com 

Contribute your unused CPU cycles to a good cause:
http://folding.stanford.edu/


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com