Textbox with quotes inteferring with SQL

[markronz]

Hello everyone-
I have a form which users type information into. Once then click the submit button, my VBA code creates a string from whatever inforamtion they typed in and creates an INSERT sql query. The problem is that users sometimes type in quotation mark (") into the textboxes. My problem is that when I form my SQL query and there is a quote in there it crashes since it thinks that's the end of the string. Does anyone have any idea how to insert a quotation into my database then? I've been trying to find a way to replace the quotes somehow, but have been unsuccessful. Please let me know what you think I should do, or if you need more information

Thanks
-Mark

 

[Remou]

A double quote should work:

Replace(ThisField,chr(34),chr(34) & chr(34))

 

[genomon]

I think the double quote is the problem (this is Access, not SQL Server) as described in the original post. If so, you need to replace the double quotes WITHIN THE TEXT to a single: Replace(txtField """, "'").

Knowledge is knowing a tomato is a fruit; Wisdom is not putting it in a fruit salad.

 

[genomon]

On further review, and according to "Access Comprehensive Enhanced" by Adamski/Hommel/Finnegan, the correct way to store double quotes on the database is to replace the double quote with two consecutive double quotes within the string.

Replace(txtField """, """").

(The above code does the same as Remou's example)

Sorry for the multiple posts, but

A double quote should work

threw me, as I was thinking TWO double quotes.
Should have taken a closer look at Remou's code!!
Sorry for the confusion, & good luck!

Knowledge is knowing a tomato is a fruit; Wisdom is not putting it in a fruit salad.

 

[Remou]

It was indeed badly phased but somehow a double double quote does not sound much better.

 

[genomon]

Sort of like the Department of Duplicate Redundancy Department, or, my favorites: ASP Pages and PIN Numbers...

Knowledge is knowing a tomato is a fruit; Wisdom is not putting it in a fruit salad.

 

[JoeAtWork]

Double Quotes = Quotation Marks ?

 

[genomon]

"

This is the character that gets replaced by two of itself.

Knowledge is knowing a tomato is a fruit; Wisdom is not putting it in a fruit salad.