• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

embedded single quotes in data upsetting insert into

Ronaldo

New Member
Hello. I have some embedded (single) quotation marks in my data source that I am reading char by char and stringing together to create a suitable DoCmd such as below. Problem is that these embedded quote's cause a problem with the quote's necessary for the command itself. Is there an escape sequence or something I can use. I need to preserve them in the data


DoCmd.RunSQL "insert into tableName(field1, field2) values('var1','var2')"


For example, if var1 = "ST DAVID'S PLACE" I end up with


DoCmd.RunSQL "insert into tableName(field1, field2) values('ST DAVID'S PLACE','var2')"


ie too many quotes !!
 

Ronaldo

New Member
It's OK I have sorted this now. Just examine each character read (sChar) and if single quote encountered, simply add another


Chr(39) & sChar


Ta
 

keymaster

New Member
hmm you can also use substitute to replace single quotes with 2 single quotes. But it has a draw back of actually making 4 single quotes if there are any 2 single quotes in the text.
 
Top