O'Connor, Patricia (OTDA)
Patricia.O'Connor at otda.state.ny.us
Fri Mar 7 15:27:26 CST 2008
What is he using to type his sql code? IF he is using WORD then make sure the AUTOCORRECT -> "Straight quotes" with "smart quotes" Is NOT checked.... I know sounds weird but that caused me all sorts of problems with some SQL code I had for Access,SQL and ORACLE. The only other thing I would say would be IF he is doing this though VBA/VB etc then Use CHR(34) for double quote" or CHR(39) for single quote ' strSQL = CHR(34) & "My last name is O" & CHR(39) &CHR(39) & "Connor" I would have to see his code to try it out here Good luck Patti ************************************************** * Patricia O'Connor * Information Technology Specialist 3 (Programming) * OTDA - BDMA * (W) mailto:Patricia.O'Connor at otda.state.ny.us * (w) mailto:aa1160 at nysemail.state.ny.us ************************************************** > -------------------------------------------------------- This e-mail, including any attachments, may be confidential, privileged or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system. -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of > Rocky Smolin at Beach Access Software > Sent: Friday, March 07, 2008 03:53 PM > To: 'Access Developers discussion and problem solving' > Subject: Re: [AccessD] FW: CodeL > > I forward and find out. Never saw that before but that don't > mean nothin'. > Lots of stuff in Access *I* never saw before. > > Rocky > > > > > > > > > -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of > Mark A Matte > Sent: Friday, March 07, 2008 12:19 PM > To: Access Developers discussion and problem solving > Subject: Re: [AccessD] FW: CodeL > > > Can you use the pipe(|) as a delimeter? > > > > From: rockysmolin at bchacc.com > > To: accessd at databaseadvisors.com > > Date: Fri, 7 Mar 2008 12:12:22 -0800 > > Subject: Re: [AccessD] FW: CodeL > > > > Mark: > > > > I'm using apostrophe and single quote interchangeably. > > > > The data contains an occurrence of a double apostrophe. > > > > Whether he delimits it in the SQL statement with a double quote (by > > using a double double quote) or an apostrophe, he gets an error. > > > > Regards, > > > > Rocky > > > > > > > > > > > > > > > > > > -----Original Message----- > > From: accessd-bounces at databaseadvisors.com > > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mark A > > Matte > > Sent: Friday, March 07, 2008 11:40 AM > > To: Access Developers discussion and problem solving > > Subject: Re: [AccessD] FW: CodeL > > > > > > > > Is there a difference between and apostrophe and a single quote? > > > > I think the delimeter is the problem...you are using a > > delimeter...that is also text in your data? > > > > Mark A. Matte > > > > > >> From: rockysmolin at bchacc.com > >> To: accessd at databaseadvisors.com > >> Date: Fri, 7 Mar 2008 11:19:30 -0800 > >> Subject: Re: [AccessD] FW: CodeL > >> > >> Gustav: > >> > >> Within the data that he's trying to insert into the SQL > table there > >> is a pair of apostrophes. Will your code have the effect > of replacing > >> them? I'm not sure he wants to replace the apostrophes with quote > >> marks. Are apostrophes not allowed in a SQL character field? > >> > >> Thanks and regards, > >> > >> Rocky > >> > >> > >> > >> > >> > >> > >> > >> > >> -----Original Message----- > >> From: accessd-bounces at databaseadvisors.com > >> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav > >> Brock > >> Sent: Friday, March 07, 2008 10:55 AM > >> To: accessd at databaseadvisors.com > >> Subject: Re: [AccessD] FW: CodeL > >> > >> Hi Rocky > >> > >> It is probably double single-quotes and not single > double-quotes that > >> are needed. > >> Try with: > >> > >> "Set [Memo] = '" & Replace(vMemo, "'", "''") & "' " & _ > >> > >> /gustav > >> > >> > >>>>> rockysmolin at bchacc.com 07-03-2008 19:44>>> > >> Dear List: > >> > >> Client (see below) is connecting to a SQL back end with ADO (he's > >> doing his own programming and calling me occasionally for help). > >> > >> When trying to update a memo field with embedded > apostrophes he get a > >> syntax error. He says that when he connected with ODBC he > didn't have > >> the > > problem. > >> > >> The code below errors on the Conn.Execute with "incorrect syntax". > >> > >> I told him to change the apostrophe delimiters around > vMemo to double > >> quote marks. Then he gets an error which says 'can't be longer than > >> 128 characters. Memo field is defined as nvarChar 4000. > >> > >> I'm pretty weak in SQL and ADO. Is there a solution to this? > >> > >> MTIA > >> > >> Rocky > >> > >> 'ADO connection to MS Sql: > >> > >> vMemo is a declared Variant that is loaded with the memo filed and > >> then when dirty, I am attempting to update the SQL back > end. CLID is > >> the integer ID number of the current client > >> > >> Conn.Execute "UPDATE tClients " & _ > >> "Set [Memo] = '" & vMemo & "' " & _ > >> "WHERE ClientIndex=" & CLID & "" > >> > >> > >> > >> -- > >> AccessD mailing list > >> AccessD at databaseadvisors.com > >> http://databaseadvisors.com/mailman/listinfo/accessd > >> Website: http://www.databaseadvisors.com > >> > >> No virus found in this incoming message. > >> Checked by AVG Free Edition. > >> Version: 7.5.516 / Virus Database: 269.21.6/1316 - Release Date: > >> 3/6/2008 > >> 6:58 PM > >> > >> > >> -- > >> AccessD mailing list > >> AccessD at databaseadvisors.com > >> http://databaseadvisors.com/mailman/listinfo/accessd > >> Website: http://www.databaseadvisors.com > > > > _________________________________________________________________ > > Shed those extra pounds with MSN and The Biggest Loser! > > http://biggestloser.msn.com/ > > -- > > AccessD mailing list > > AccessD at databaseadvisors.com > > http://databaseadvisors.com/mailman/listinfo/accessd > > Website: http://www.databaseadvisors.com > > > > No virus found in this incoming message. > > Checked by AVG Free Edition. > > Version: 7.5.516 / Virus Database: 269.21.6/1316 - Release Date: > > 3/6/2008 > > 6:58 PM > > > > > > -- > > AccessD mailing list > > AccessD at databaseadvisors.com > > http://databaseadvisors.com/mailman/listinfo/accessd > > Website: http://www.databaseadvisors.com > > _________________________________________________________________ > Shed those extra pounds with MSN and The Biggest Loser! > http://biggestloser.msn.com/ > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > > No virus found in this incoming message. > Checked by AVG Free Edition. > Version: 7.5.516 / Virus Database: 269.21.6/1316 - Release > Date: 3/6/2008 > 6:58 PM > > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > >