Using apostrophe causes syntax error in text field

[XP]

Using Office 2003 and Windows XP;

I have found using Textboxes that if an apostrophe (') is entered, this
causes an error (syntax error in expression) when the program attempts to
write the contents into the table.

In all cases I'm using an UPDATE to process the changes. The underlying
field data types are corresponding: Text (255); and Memo.

How can I correct this issue? Also, are there other characters I need to be
aware of? And if so, how do you handle this? Best practice?

Thanks much in advance.

 

[fredg]

Using Office 2003 and Windows XP;

I have found using Textboxes that if an apostrophe (') is entered, this
causes an error (syntax error in expression) when the program attempts to
write the contents into the table.

In all cases I'm using an UPDATE to process the changes. The underlying
field data types are corresponding: Text (255); and Memo.

How can I correct this issue? Also, are there other characters I need to be
aware of? And if so, how do you handle this? Best practice?

Thanks much in advance.

Post your actual SQL. It will be easier to explain.

 

[Damian S]

Hi XP,

Ah, the dreaded apostrophe problem... yes, it will create issues for you if
you are attempting to create an update/insert SQL statement.

To get around this, use the replace function like this:

replace(me.FIELDWITHAPOSTROPHE, "'", "''")

which is replacing a single apostrophe with two single apostrophes.

Hope this helps.

Damian.