SQL, DCount, and DLookup - The Tyranny of the Apostrophe

[Guest]

So I've got a few tables that track names & addresses & such, and I use the
AfterUpdate event to search for duplicate entries. That way, when someone
tries to enter in Bob Jones when one already exists in the database, they'll
get this little warning message that they might be creating a duplicate. I
run these checks with the DCount command. An example would be:

if DCount("*","contacts","[index] <> " & me!index & " AND [first] = '" &
me!first & "' AND [last] = '" & me!last & "'") > 0 then
doCmd.Beep
msgbox "A person by the name of " & me!first & " " & me!last & " already
exists! Please check to make sure you are not creating a duplicate
entry.",vbInformation,"PossibleDuplicate Entry"
end if

So from what I can tell, DCount creates an SQL statement based off of the
arguments. The third argument of the DCount would look something like this:

"[index] <> 312 AND [first] = 'Bob' AND [last] = 'Jones'"

Of course, for Bob Jones and 99% of the people in the world (or at least in
the US) this will work just fine. We have a little problem when someone with
the name O'Connell comes in, though. You can probably tell why:

"[index] <> 312 AND [first] = 'Tim' AND [last] = 'O'Connell'"

When the engine looks at this, it just totally freaks out, because the
apostrophe in O'Connell will mark the end of the string! As far as SQL is
concerned, we're looking for someone with the last name 'O'. The rest of the
statement (Connell') is considered to be some unknown argument or extention
to the WHERE clause, and last I checked, Connell' is not a valid SQL command.

So the problem is this: how do you use SQL to search for a table entry when
an apostrophe is involved? Is there some sort of special character I can use
to mark it as an actual apostrophe and not a string marker?

Thanks in advance!

Nick

 

[Douglas J. Steele]

if DCount("*","contacts","[index] <> " & me!index & " AND [first] = '" &
me!first & "' AND [last] = '" & Replace(me!last, "'", "''") & "'") > 0 then

Exagerated for clarity, that Replace statement is

Replace(me!last, " ' ", " ' ' ")

Do the same for the first name if there's a chance that it might have
include an apostrophe.

 

[Guest]

Swweeeeet! So a double apostrophe does the trick? Thanks a ton Douglas!

And very random and off topic... ever watch Strongbad Emails on Homestar
Runner's site? There was a pretty funny episode where Strongbad tried to get
Strongmad to say "Douglas"...

lol, sorry, couldn't help it.

tyvm!

Nick

if DCount("*","contacts","[index] <> " & me!index & " AND [first] = '" &
me!first & "' AND [last] = '" & Replace(me!last, "'", "''") & "'") > 0 then

Exagerated for clarity, that Replace statement is

Replace(me!last, " ' ", " ' ' ")

Do the same for the first name if there's a chance that it might have
include an apostrophe.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

So I've got a few tables that track names & addresses & such, and I use
the
AfterUpdate event to search for duplicate entries. That way, when someone
tries to enter in Bob Jones when one already exists in the database,
they'll
get this little warning message that they might be creating a duplicate.
I
run these checks with the DCount command. An example would be:

if DCount("*","contacts","[index] <> " & me!index & " AND [first] = '" &
me!first & "' AND [last] = '" & me!last & "'") > 0 then
doCmd.Beep
msgbox "A person by the name of " & me!first & " " & me!last & " already
exists! Please check to make sure you are not creating a duplicate
entry.",vbInformation,"PossibleDuplicate Entry"
end if

So from what I can tell, DCount creates an SQL statement based off of the
arguments. The third argument of the DCount would look something like
this:

"[index] <> 312 AND [first] = 'Bob' AND [last] = 'Jones'"

Of course, for Bob Jones and 99% of the people in the world (or at least
in
the US) this will work just fine. We have a little problem when someone
with
the name O'Connell comes in, though. You can probably tell why:

"[index] <> 312 AND [first] = 'Tim' AND [last] = 'O'Connell'"

When the engine looks at this, it just totally freaks out, because the
apostrophe in O'Connell will mark the end of the string! As far as SQL is
concerned, we're looking for someone with the last name 'O'. The rest of
the
statement (Connell') is considered to be some unknown argument or
extention
to the WHERE clause, and last I checked, Connell' is not a valid SQL
command.

So the problem is this: how do you use SQL to search for a table entry
when
an apostrophe is involved? Is there some sort of special character I can
use
to mark it as an actual apostrophe and not a string marker?

Thanks in advance!

Nick