Apostrophe Issue With SQL Insert Command 1

[nag9127]

I have a problem with a SQL insert command. The command runs fine except when it encounters a piece of data that has an apostrophe ('). It looks as if the software is seeing that as a delimiter and returning a syntax error message. When I remove the character, the script runs fine. I can't expect my users to not use that character. Can someone propose a solution or a workaround to this problem? This is Access 2000. Thanks!

 

[MikeC14081972]

SQL uses the apostrophe to denote a string. Your insert command is seeing everything after the ' until the next ' as a string.

You need to stop data being passed to the tables with ' in the field. wrap the field in the Insert query in the replace function

try

Insert INTO tbl_Table ([Field]) VALUES (Replace (<your string>,"'",""))

http://www.hanlew.co.uk

 

[nag9127]

Thanks for the reply! I did a search on this after I posted and I found various posts indicating this solution. As a followup question am I changing this data temporarily just to make the script work or am I changing it permanently?

 

[MikeC14081972]

Where is your data coming from. If it is a form that writes data to a table I would add a replace function to the AfterUpdate event of the field

txtField = Replace(txtField,"'","")

That way by the time the data is written to the table the offending character has been removed.

In the example in the reply above the ' will be removed from the string that is being inserted to the table so it would be a permanent change.

http://www.hanlew.co.uk

 

[PHV]

problem with a SQL insert command
could you please post the code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[nag9127]

MikeC14081972:

That's exactly where I was headed with my question. If the change is permanent, I think I would want to change it back afetr the insert is accomplished. Thanks for your help! I'll proceed with this and post back if I can't get it done!

 

[PHV]

nag, you don't have to remove any apostrophe !
why not posting your actual code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886