Quotes & apostrophes in Select statements

[Gilbert 2097]

Hi all... Using Access 2003 VBA I'm doing a conversion from Dbase and I have
set up the following recordset where Lname is the last name being searched
for -
Set rst=CurrentDb.openrecordset("SELECT DonorID,FirstName,LastName FROM
Donors WHERE LastName = '" & Lname & "';")
It works fine except when I get a name with an apostrophe in it like
D'Agostino or D'Ambrosia. It thinks the apostrophe is the end of the search
string and bombs out...How do you allow for this type of thing?? Do I have
to use some other kind of delineater or what?
Thanx for all your help out there!!!

 

[John Spencer]

I use quotation marks " instead of the apostrophe '. TO embed a quotation
mark in a string you use two in a row. So, you could do

Set rst=CurrentDb.openrecordset("SELECT DonorID,FirstName,LastName FROM
Donors WHERE LastName = """ & Lname & """;")

Alternative is to use Chr(34) to embed the quote mark

Set rst=CurrentDb.openrecordset("SELECT DonorID,FirstName,LastName FROM
Donors WHERE LastName = " & Chr(34) & Lname & Chr(34) & " ;")

Or you can double up any apostrophes in LName
Lname = Replace(Lname, "'","''")
To make that clearer I show it with extra spaces which should not be there
when you use the code.

Lname = Replace(Lname, " ' "," ' ' ")

Set rst=CurrentDb.openrecordset("SELECT DonorID,FirstName,LastName FROM
Donors WHERE LastName = '" & Lname & "';")

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Marshall Barton]

Hi all... Using Access 2003 VBA I'm doing a conversion from Dbase and I have
set up the following recordset where Lname is the last name being searched
for -
Set rst=CurrentDb.openrecordset("SELECT DonorID,FirstName,LastName FROM
Donors WHERE LastName = '" & Lname & "';")
It works fine except when I get a name with an apostrophe in it like
D'Agostino or D'Ambrosia. It thinks the apostrophe is the end of the search
string and bombs out...How do you allow for this type of thing?? Do I have
to use some other kind of delineater or what?

Use the other quote around the value:

. . . WHERE LastName=""" & Lname & """ ")

The " you want to end up with are doubled up because they
are inside the outer quotes.

Doubling up the ' in the name is an alternate (and safer)
way to deal with your problem:

. . . WHERE LastName='" & Replace(Lname, "'", "''") & "' ")

Be sure to count the ' and " carefully

 

[Gilbert 2097]

Thank you very much for your responses John & Marshall. I had thought that
you had to alternate between single and double quotes when embedding them
together; guess I was wrong. Thnax again