Access form problem

[Hecate]

I'm not sure if this is the right forum for this but I can't find anywhere more appropriate.

Our help desk system uses an Access database. To speed up data entry for small jobs I created a form which allows the tech to enter a comment and close the call without going through the tedious process of opening the job and completing the fields manually. It works beautifully as long as the comment string doesn't contain an apostrophe but if it does it errors.

 

The SQL I'm using is "INSERT INTO tblProgress ([NewDate],[JobNo],[Progress],[inits]) VALUES (Date(), " & JBN & ",' "& Com &" ','" & Idn & "');"

where Com is the comment string taken from the input form. (the other fields are completed automatically) I know apostrophes are a problem area in SQL but can anyone suggest how I can allow users to use apostrophes without triggering an error.

 

The error is, run time error 3075, syntax error (missing operator) in query expression "doesn't work'

 

Using 'not working' instead of 'doesn't work' is fine but I can't expect users to know that

[Jamo]

My thoughts would be parse the string in the text box before the SQL query is run. By parsing the text string you could remove any charachters which are illegal

[tommccann]

could i see your database and forms??

 

but @Jamo parsing the input would in theory fix the illegal expression

[Hecate]

I've found the answer - replace the " ' " with " ' ' ".

The actual syntax is Com = Replace(Com, "'", "''").

 

Thanks for your comments

 

Tom did you want to see the forms etc anyway or were you just interested to help me find the solution

Edited December 14, 2009 by Hecate
additional comment

[tommccann]

No id love to see it if you dont mind!!

 

pm it me, or upload it here! Thanks