The dreaded apostrophe

[hansdbase]

I have a form with a text field and a command button. When text is entered into the field and the command button is pressed it adds the text to the table to the appropriate field. But if the text contains an apostrophe, I of course get an error. I put the text variable in [] but it still gives me an error. Is there any easy way around this? Thanks.

[June7]

Why do you get error? Are you running SQL in VBA? [] would not be appropriate for delimiting a VBA variable.

Why would you need any code for this data entry? Are you not using bound form?

[hansdbase]

It is not set up as a bound form. It is a vba sql command. Is there any way around this apostrophe dilemma in such a case?

[June7]

Options:

1. use Replace() function to escape apostrophe, like:

CurrentDb.Execute "INSERT INTO table(field) VALUES(" & Replace(textbox, "'", "''") & ")"

2. use recordset and its AddNew and Update methods then apostrophe doesn't matter

[CJ_London]

shouldn't that be?

CurrentDb.Execute "INSERT INTO table(field) VALUES('" & Replace(textbox, "'", "''") & "')"

[hansdbase]

I am curious about your question. Shouldn't what be? Thank you to June and you for the Replace function

[CJ_London]

I am curious about your question. Shouldn't what be?
VALUES(" & Replace(textbox, "'", "''") & ")"needs the single quotes
VALUES('" & Replace(textbox, "'", "''") & "')"

[June7]

Good point. Since this is a text field, yes, apostrophe (single quote) delimiters. However, # if field is date/time, nothing for number type and of course no Replace() for these data types.