[AccessD] Escaping a Single Quotation Mark in a pass through update query

Hi Borge

You may have to double the single quotes:

  strLog = "Houston - We've got massive problems!"
  strLog = Replace(strLog, "'", "''")

By the way, wouldn't you need a CR+LF and not just a CR here:

SET ExceptionLog = '" & strLog & "' + CHAR(13) + CHAR(10) +

/gustav

>>> pcs at azizaz.com 16-01-2008 11:21:24 >>>
Someone please help:

I want to update a column with a string that contains a 
Single Quotation Mark using a pass through query to SQL2005

The code bit with the SQL string looks like:

Dim strSQL As String
Dim strLog As String
Dim strOK As String

strLog = "Houston - We've got massive problems!"

strSQL = "UPDATE RMS_DATA.dbo.tblBookingRequestExceptionLog 
SET ExceptionLog = '" & strLog & "' + CHAR(13) + 
ExceptionLog;"

strOK = fncExecuteSP(strSQL)

As long as the strLog does not contain a single quotation 
mark the strSQL updates OK.

I've lost patience on this - help please!!

borge