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
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