VBA, WHERE CLAUSE IN SELECT STATEMENT

WHERE CLAUSE IN SELECT STATEMENT

Author	Message
Chaplain Doug Pruiet #1 / 7	WHERE CLAUSE IN SELECT STATEMENT I have code that sets up a WHERE clause as: Criteria = "[DIN]= '" & DIN$ & "' AND [LASTNAME]='" & LAST$ & "'" strSQL = "SELECT * FROM [Inmates] WHERE " & Criteria Set rst = dbs.OpenRecordset(strSQL) The problem is that there are names like O'KELLY that come up and SQL gives an error because the WHERE clause gives: WHERE [LASTNAME]='O'KELLY' How can I set it up so names with apostrophes do not bomb? Thanks.
Sat, 20 Aug 2005 22:49:55 GMT

Tony Oakle #2 / 7	WHERE CLAUSE IN SELECT STATEMENT Quote: > I have code that sets up a WHERE clause as: > Criteria = "[DIN]= '" & DIN$ & "' AND [LASTNAME]='" & > LAST$ & "'" > strSQL = "SELECT * FROM [Inmates] WHERE " & Criteria > Set rst = dbs.OpenRecordset(strSQL) > The problem is that there are names like O'KELLY that come > up and SQL gives an error because the WHERE clause gives: > WHERE [LASTNAME]='O'KELLY' > How can I set it up so names with apostrophes do not > bomb? Thanks. there's several way, I use this one: Criteria = "[DIN]= """ & DIN$ & """ AND [LASTNAME]=""" & LAST$ & """" strSQL = "SELECT * FROM [Inmates] WHERE " & Criteria Set rst = dbs.OpenRecordset(strSQL) or you could use Criteria = "[DIN]= " & chr(34) & DIN$ & chr(34) & " AND [LASTNAME]=" & chr(34) & LAST$ & chr(34) strSQL = "SELECT * FROM [Inmates] WHERE " & Criteria Set rst = dbs.OpenRecordset(strSQL) -- Tony Oakley (Microsoft Access MVP) GSXR1300R Hayabusa 300hr 240GB TiVo
Sat, 20 Aug 2005 23:01:25 GMT

Van T. Din #3 / 7	WHERE CLAUSE IN SELECT STATEMENT Try: Criteria = "[DIN] = " & Chr$(34) & DIN$ & Chr$(34) & _ " AND [LASTNAME] = " & Chr$(34) & LAST$ & Chr$(34) strSQL = "SELECT * FROM [Inmates] WHERE " & Criteria Set rst = dbs.OpenRecordset(strSQL) -- HTH Van T. Dinh MVP (Access) Quote: > I have code that sets up a WHERE clause as: > Criteria = "[DIN]= '" & DIN$ & "' AND [LASTNAME]='" & > LAST$ & "'" > strSQL = "SELECT * FROM [Inmates] WHERE " & Criteria > Set rst = dbs.OpenRecordset(strSQL) > The problem is that there are names like O'KELLY that come > up and SQL gives an error because the WHERE clause gives: > WHERE [LASTNAME]='O'KELLY' > How can I set it up so names with apostrophes do not > bomb? Thanks.
Sat, 20 Aug 2005 23:08:23 GMT

Chaplain Doug Pruiet #4 / 7	WHERE CLAUSE IN SELECT STATEMENT I have implemented the chr(34) solution. Thank you both. Quote: >-----Original Message----- >I have code that sets up a WHERE clause as: >Criteria = "[DIN]= '" & DIN$ & "' AND [LASTNAME]='" & >LAST$ & "'" >strSQL = "SELECT * FROM [Inmates] WHERE " & Criteria >Set rst = dbs.OpenRecordset(strSQL) >The problem is that there are names like O'KELLY that come >up and SQL gives an error because the WHERE clause gives: >WHERE [LASTNAME]='O'KELLY' >How can I set it up so names with apostrophes do not >bomb? Thanks. >.
Sun, 21 Aug 2005 00:56:03 GMT

Ondin #5 / 7	WHERE CLAUSE IN SELECT STATEMENT You need to include something like this: "WHERE tblContacts.ContShort like " & """" & Criteria & "" & """ ORDER BY....." You need the wrap the criteria in a whole load of double- quotes to avoid this problem. Quote: >-----Original Message----- >I have code that sets up a WHERE clause as: >Criteria = "[DIN]= '" & DIN$ & "' AND [LASTNAME]='" & >LAST$ & "'" >strSQL = "SELECT FROM [Inmates] WHERE " & Criteria >Set rst = dbs.OpenRecordset(strSQL) >The problem is that there are names like O'KELLY that come >up and SQL gives an error because the WHERE clause gives: >WHERE [LASTNAME]='O'KELLY' >How can I set it up so names with apostrophes do not >bomb? Thanks. >.
Sun, 21 Aug 2005 18:12:07 GMT

Page 1 of 1

[ 7 post ]