Closing Delimiter error

[AkAlan via AccessMonster.com]

Hi All, I am getting an error when there is an apostrophe in the Comments
text box. I tried using double double quotes in place of the single quotes
but get a different error then. Here is the code I use, thanks for any help.


strSql = "INSERT INTO tblPmiHistory (PmiId,EquipId,Employee,StartDate,
FinishDate,DateSchedOriginal, " _
& " DateSchedCurrent,Comments,TaskId,PerfWc )" _
& " VALUES ('" & Me.PmiId & "','" & Me.EquipId & "','" & Me.Employee
& "','" & Me.StartDate & "','" _
& Me.FinishDate & "','" & Me.DateSchedOriginal & "','" & Me.
DateSchedCurrent & "','" _
& Comments & "','" & Me.TaskId & "','" & Me.txtPerfWc & "'" & ")"

 

[Sylvain Lafontaine]

When building a sql string, if you are using the single quote as the string
delimiter, then you must use double single quotes inside it; when it's
double quotes, you must use double double quotes. The situation is also
more complicated because the situation can repeat itself with the VBA
strings when you are using the double quotes for you sql string delimiters.

In your case, try replacing « Comments » with « Replace (Comments, "'",
"''") ». It's a good idea to display the result of strSql after that to
verify that everything is OK.