asked on

Select Statement using a variable with apostrophe VBA Access

I have a select statement that checks whether an address exists in the table.
Quite a few street names have apostrophes in the name

Example
Murphy's Court
Adam's Pond Road

Dim strCompare, strSQL As String
Dim db As Database
Dim rs As DAO.Recordset

strCompare = Me.sStreetNumber & " " & iStreetName.Column(1)

as an example.....
strCompare = 3 Adam's Pond Road (which plugs into the select statement)

Set db = CurrentDb
strSQL = "SELECT * FROM qryresidences WHERE Res = '" & strCompare & "' "

Set rs = db.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)
If rs.EOF = False Then
MsgBox "That address exists already"
End If

rs.Close

I keep getting an syntax error because of the apostrophe.

Any insight or help would be greatly appreciated.

stevbe

double up the single quotes should work ...

strCompare = Replace(strCompare, "'", "''")
single quote^ ^ two single quotes

ASKER CERTIFIED SOLUTION

jmantha709

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

GRayL

Jmanth709 has it right. With strCompare = "3 Adam's Pond Road". You original would reduce to:

SELECT * FROM qryresidences WHERE Res = '3 Adam's Pond Road'

By using double quotes it will reduce to:

SELECT * FROM qryresidences WHERE Res = "3 Adam's Pond Road"

which will compile correctly.

stevbe

use which ever works / you understand better is the answer you should accept.

Doubling an escape character (which is what ' and " are) works just fine..

I did a quick and dirty test for my suggestion ...

?DLookup("Address", "tblAdd", "Address='" & Replace("O'Brien", "'", "''") & "'")
O'Brien

I even tested with more than 1 single quote inside the name ...
?DLookup("Address", "tblAdd", "Address='" & Replace("O'Bri'en", "'", "''") & "'")
O'Bri'en