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

T

Tatakau

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
 
D

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top