Insert Into Statement & strings with special characters

[estelle1966]

I am creating a new record in a table in Access 2016, using data that the user has input in various forms. I'm picking up a problem with special characters in the user input - for instance, today's input was "Pack in 100's" - the apostrophe caused havoc with the INSERT INTO statement, causing it to crash with a syntax error.

It doesn't Is there any way to "wrap" the user input string so that SQL ignores whatever it contains? I'm thinking something like using # to identify a date?

TIA

[CarlettoFed]

Show insert SQL statement.

[orange]

Use 2 single quotes "Pack in 100''s"
Research Escaping quote sql

[ranman256]

use either dbl quotes ", or single ', but you cant use both.
I use double as the outer enclosure, but before you put it in the sql, use REPLACE to change any doubles in the string to single,
THEN you can sql.

[June7]

If this were a textbox bound to field, the apostrophes and quotes would not be an issue. Why using INSERT statement?

Since user won't know about doubling apostrophes, use Replace function:

Replace(textboxname, "'", "''")

An alternative that also will not have issue with apostrophe or quote marks, is to open recordset and use recordset AddNew and Update methods to create record.