single and double quotes causing problem with SQL statement

[talytech]

Hello ,

I am struggling with adding a record to the table via SQL INSERT statement where there are single and double quotes in the field.

I have a text control on a form named "frmEditTime" and the text control name is [txtComments]. It is bound to a memo field in the table.

In the txtComments control on the form, the following comment is entered : This is a test comment with J'anice Jackson "going" to the store.
My INSERT SQL errors out because of the single/double quotes. On click of a command button, I run the SQL statement below.

INSERT INTO tblTimeTrack ([comments]) SELECT Chr(34) &Nz(Forms!frmEditTime![txtComments],"") & Chr(34) & " AS Expr1;"

The error I receive is: syntax error (missing operator in query expression)


Can someone helpme and please tell me what I'm doing wrong?

[talytech]

Hi all,
FYI, I solved my issue with the following statement.

INSERT INTOtblTimeTrack ([comments]) SELECT "'" &  Nz(Replace(mForm![txtComments], "'","''"), "") "' AS Expr1;"

Hello ,

I am struggling with adding a record to the table via SQL INSERT statement where there are single and double quotes in the field.

I have a text control on a form named "frmEditTime" and the text control name is [txtComments]. It is bound to a memo field in the table.

In the txtComments control on the form, the following comment is entered : This is a test comment with J'anice Jackson "going" to the store.
My INSERT SQL errors out because of the single/double quotes. On click of a command button, I run the SQL statement below.

INSERT INTO tblTimeTrack ([comments]) SELECT Chr(34) &Nz(Forms!frmEditTime![txtComments],"") & Chr(34) & " AS Expr1;"

The error I receive is: syntax error (missing operator in query expression)


Can someone helpme and please tell me what I'm doing wrong?