Mark A Matte
markamatte at hotmail.com
Wed Jan 16 09:50:24 CST 2008
Borge, I have a SP that ran an INSERT that had a similar issue...Yes double quotes are needed...but I found the stupid thing would run 1 day...but if I changed it at all after it ran...it would fail(even with correct syntax). Even more oddly...by the time I got it to run again...the syntax was exactly what I started with. I never really understood this...but someone on the list suggested replacing all double quotes with char(34)....and I have not had a single issue since. Good Luck, Mark A. Matte > From: pcs at azizaz.com > To: accessd at databaseadvisors.com > Date: Wed, 16 Jan 2008 22:35:29 +1000 > Subject: Re: [AccessD] Escaping a Single Quotation Mark in a pass through update query > > Gustav, > Doubling is what is needed > > (Doubling was one of the first things I did - but somehow I > convinced myself it didn't work - and I never went back to > check again - one of those moments sshhhh!) > > I tested and found that CHAR(13) is adequate to cause a line > break if you paste the contents of the nvarchar(max) field > into a Word document, whereas for a line break to appear > when viewing the column as a memo field in Access; and > similar if pasting contents into a .txt file you require the > CHAR(13) + CHAR(10) in order to get a line break. > > Thanks! > > borge > > ---- Original message ---- >>Date: Wed, 16 Jan 2008 11:49:37 +0100 >>From: "Gustav Brock" >>Subject: Re: [AccessD] Escaping a Single Quotation Mark in > a pass through update query >>To: >> >>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 >> >> >>-- >>AccessD mailing list >>AccessD at databaseadvisors.com >>http://databaseadvisors.com/mailman/listinfo/accessd >>Website: http://www.databaseadvisors.com > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com _________________________________________________________________ Share life as it happens with the new Windows Live. http://www.windowslive.com/share.html?ocid=TXT_TAGHM_Wave2_sharelife_012008