Include commas etc within SQL INSERT...INTO statement

Question

I'm running Office 2007 Microsoft Access on Windows XP...

I have an unbound text box, on a form, into which the operator can type a message, notes etc.  There are no restrictions on punctuation used and commas, quotation marks, forward slashes etc which are necessary within the scope of the information that is likely to be entered.

I want to pick this text up and use it in an INSERT INTO SQL statement and enter it, along with other information, into a memo field within a table.  However, simply using the syntax:

"INSERT INTO tblFeedback (Notes) VALUES (" & Me.txtbox_1 & ")"

produces errors that are attributable to a variety of punctuation issues.  Is there any way that I can 'wrap' the whole text up so that the query doesn't notice the punctation that confuses it?  I could search for each likely character and insert speech marks etc round it but that seems a bit heavy duty.  It also only catches characters that I happen to have included in the search and replace function.

Any ideas?

Thanks in anticipation.

Geoff

Answer 1

Since Notes is presumably a text field, you need to enclose what you're trying to insert in quotes:

"INSERT INTO tblFeedback (Notes) VALUES ('" & Me.txtbox_1 & "')"

In case it's not obviously, exagerated for clarity that's

" INSERT INTO tblFeedback (Notes) VALUES ( ' " & Me.txtbox_1 & " ' ) "

If there's a chance that there might be an apostrophe in what you're inserting, use the following instead:

"INSERT INTO tblFeedback (Notes) VALUES (""" & Me.txtbox_1 & """)"

(that's three double-quotes in a row)

If there's a chance that there might be double quotes in what you're inserting, use the following instead:

"INSERT INTO tblFeedback (Notes) VALUES ('" & Replace(Me.txtbox_1, "'", "''") & "')"

Again, exagerated for clarity, that's

" INSERT INTO tblFeedback (Notes) VALUES ( ' " & Replace(Me.txtbox_1, " ' ", " ' ' ") & " ' ) "