Problem with SQL insert statement and apostrophe 1

[needadvice]

I have a data entry form with a field named [Statement].
The entry in this field is then inserted in a table via an sql insert statement. If the [statement] field contains an apostrophe it woun't insert.
I know it has something to do with strQuote = Chr$(34) but I don't know exactly how to fix this.
Any help greatly appreciated.

 

[dodge20]

It would help if you post your code. But you may just need to put quotes around you apostrophe. It is probably reading the ' as the beginning of a value eg.

where date = '01/

Dodge20

 

[LittleSmudge]

This sounds like you have something like

"INSERT INTO tblName ( field1, field2, .. .. etc )
  VALUES ('" & [Statement] & "', value2 .. .. etc )"

When JET parses this the first single quote is seen as the start of the text and the apostrophe as the end of the text.
Whatever comes after the apostrophe is seen as a problem.

You need to replace the single quotes either side of the text with double double quotes. Ie.

""

So the example above becomes

"INSERT INTO tblName ( field1, field2, .. .. etc )
  VALUES (""" & [Statement] & """, value2 .. .. etc )"

'ope-that-'elps.




G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

 

[needadvice]

Here is my insert statement. I'm not sure I understand where I would put the quotes. I tried in the data entry field but that didn't work.

DoCmd.RunSQL ("insert into 189TBL([DateofClaim],[CWEdate],[BenifitsPaid],[SSN],[wageearning],[Garnishment],[Tax],[RefundType],[Statement],[FName],[LName],[CITY],[STATE],[ZIP],[STREET],[DateofMailing],[AMOUNTOVERPAID],[LocalOffice],[ProgramCode],[OLDWBR],[NEWWBR],[PBENEFITS],[ORIGBENEFITSPAID])Values(#" & DCLAIM & "#, #" & CWEDATE & "#," & BENPAID & ",'" & SSN & "'," & WAGE & "," & GARNISHMENT & "," & TAX & ",'" & REFUNDTYPE & "','" & STATEMENT & "','" & FNAME & "','" & LNAME & "','" & CITY & "','" & STATE & "','" & ZIP & "','" & STREET & "', #" & DATEOFMAILING & "#,'" & LOCALOFFICE & "','" & OLDWBR & "','" & NEWWBR & "','" & AMOUNTOVERPAID & "','" & PROGRAMCODE & "','" & PBENEFITS & "','" & BENPAID & "')&quot

 

[needadvice]

Worked!
Thank You GLS