VBA, Nightmares with the apostrophe!!!!!!!!!!!!

Nightmares with the apostrophe!!!!!!!!!!!!

Author	Message
Robert Bourqu #1 / 8	Nightmares with the apostrophe!!!!!!!!!!!! I have a function which receives 4 string variables (pr,no,nu,ru) and creates an SQL statement which includes the 4 variables. "SELECT * FROM TtableX WHERE Condition1 = '" & ru & "' AND..." Everytime any of the variables contains an apostrophe, I get the Syntax Error message. I've consulted many pages on the Internet I've tried the replace() funct. in so many ways... replace(ru,"'","''")(2 apostrophes) replace(ru,"'","""")(2 quotation marks) replace(ru,"'",""'"")(apostrophe within quotation marks) etc. Has anybody found any other way to deal with this? In advance, thanks -- Robert
Sun, 11 Sep 2005 04:56:01 GMT

Rick Brand #2 / 8	Nightmares with the apostrophe!!!!!!!!!!!! Quote: > I have a function which receives 4 string variables (pr,no,nu,ru) and > creates an SQL statement which includes the 4 variables. > "SELECT * FROM TtableX WHERE Condition1 = '" & ru & "' AND..." > Everytime any of the variables contains an apostrophe, I get the Syntax > Error message. > I've consulted many pages on the Internet > I've tried the replace() funct. in so many ways... > replace(ru,"'","''")(2 apostrophes) > replace(ru,"'","""")(2 quotation marks) > replace(ru,"'",""'"")(apostrophe within quotation marks) > etc. > Has anybody found any other way to deal with this? Replacing each single apostrophe with two apostrophes has always worked for me.
Sun, 11 Sep 2005 05:19:08 GMT

Van T. Din #3 / 8	Nightmares with the apostrophe!!!!!!!!!!!! Use the good old Chr$(34) "SELECT * FROM TtableX WHERE Condition1 = " & _ Chr$(34) & ru & Chr$(34) & " AND..." -- HTH Van T. Dinh MVP (Access) Quote: > I have a function which receives 4 string variables (pr,no,nu,ru) and > creates an SQL statement which includes the 4 variables. > "SELECT * FROM TtableX WHERE Condition1 = '" & ru & "' AND..." > Everytime any of the variables contains an apostrophe, I get the Syntax > Error message. > I've consulted many pages on the Internet > I've tried the replace() funct. in so many ways... > replace(ru,"'","''")(2 apostrophes) > replace(ru,"'","""")(2 quotation marks) > replace(ru,"'",""'"")(apostrophe within quotation marks) > etc. > Has anybody found any other way to deal with this? > In advance, thanks > -- > Robert
Sun, 11 Sep 2005 23:04:21 GMT

Tim Ferguso #4 / 8	Nightmares with the apostrophe!!!!!!!!!!!! Quote: > "SELECT * FROM TtableX WHERE Condition1 = '" & ru & "' AND..." Well: look at the string that SQL sees - WHERE Condition1 = 'O'Hare Airport' AND... if you were the interpreter, where would you stop reading? This works fine: WHERE Condition1 = 'O''Hare Airport' AND... or WHERE Condition1 = "O'Hare Airport" AND... or even WHERE Con1 = "He said, ""It's 'O'Hare', stoopid!""" etc etc. Therefore, when you wrap the whole thing in a VBA string, you have to double up whatever you use on the outside: "WHERE Con1 = 'O''Hare International' AND..." or "WHERE Con1 = ""O'Hare International"" AND..." for myself, I always use the double quote, so I have less to remember, but it's personal preference. Of course, I got really screwed in MSDE, which _only_ accepts single quotes for strings... :-( HTH Tim F
Mon, 12 Sep 2005 03:44:28 GMT

Sherry Dahl-Monte #5 / 8	Nightmares with the apostrophe!!!!!!!!!!!! All of the other postings have good suggestions...I would like to include the option of checking for & t{filter} out apostrophes from the string before you send it to SQL. We use this option when we can't control the data entry to the field, and when we won't know if the field contains an apostrophe. Don't replace the apostrophe with anything, get rid of them altogether! Quote: >-----Original Message----- >I have a function which receives 4 string variables (pr,no,nu,ru) and >creates an SQL statement which includes the 4 variables. >"SELECT * FROM TtableX WHERE Condition1 = '" & ru & "' AND..." >Everytime any of the variables contains an apostrophe, I get the Syntax >Error message. >I've consulted many pages on the Internet >I've tried the replace() funct. in so many ways... >replace(ru,"'","''")(2 apostrophes) >replace(ru,"'","""")(2 quotation marks) >replace(ru,"'",""'"")(apostrophe within quotation marks) >etc. >Has anybody found any other way to deal with this? >In advance, thanks >-- >Robert >.
Mon, 12 Sep 2005 08:08:44 GMT

Tim Ferguso #6 / 8	Nightmares with the apostrophe!!!!!!!!!!!! Quote: > Don't replace the apostrophe with anything, > get rid of them altogether! I'm not sure that the O'Connors and the O'Mallys would like that at all; of course, they also get stuffed by designers that don't bother about the capitalisation either. If you take the apostrophe out of "T' Pao", it simply won't validate against any normal list of CD artists. In addition, you will change the meaning of entries like <He called "It's dog"> which is completely different from <He called its dog> I figure that's a high price to pay for not being bothered to add a line or two of code... Tim F
Tue, 13 Sep 2005 03:07:39 GMT

Michael Rochle #7 / 8	Nightmares with the apostrophe!!!!!!!!!!!! I find it easiest to use a function, so that I can do this: strSQL = "SELECT * FROM tTableX WHERE ([tTableX].[Surname] = " & _ mStrQuote("O'Connor") & ");" The underscore character (_) at the end of the first line is so Access knows the line continues. ---------------------------------------------------------------------- Public Function mStrQuote(ByVal varVal As Variant) As String On Error Resume Next mStrQuote = vbNullString varVal = Trim(varVal) If Not mChkIsNothing(varVal) Then mStrQuote = varVal If (varVal Like Chr$(34) & "*" & Chr$(34)) Then Exit Function mStrQuote = Chr$(34) & varVal & Chr$(34) End If End Function 'mStrQuote ---------------------------------------------------------------------- Public Function mChkIsNothing(ByVal varVal As Variant) As Boolean On Error Resume Next mChkIsNothing = True varVal = Trim(varVal) If IsNull(varVal) Or IsEmpty(varVal) Then Exit Function If Len(varVal) < 1 Then Exit Function mChkIsNothing = False End Function 'mChkIsNothing ---------------------------------------------------------------------- Quote: > Everytime any of the variables contains an apostrophe, I get the Syntax > Error message... > Has anybody found any other way to deal with this?
Wed, 14 Sep 2005 11:14:46 GMT

Page 1 of 1

[ 8 post ]