Help with hyphen in last name

[AL]

I have a code behind a last name field [LastName], that
checks if the database has similar last names. The problem
is that some times an old client(a year or 2 old) comes
back and uses a hyphen(-) in his last name which of course,
will be viewed as different name. How can I avoid this problem?
thanks
Al

 

[Ken Snell]

What do you want to avoid -- not let the client change his/her last name?
not allow the entry of a last name that contains a hyphen? identify the part
of the last name to the left (or right) of the hyphen and then look for a
match?

Please provide us with more details. Note that the use of a last name as a
"unique" identifier has severe limitations for most databases. Can you use a
different identifier to more easily identify a client? or perhaps use the
last name to look up possible clients and choose the one you want?

 

[Al]

The situation is not like that. here is the details. This
db is for a translation co. and every now and then they get
lists of names from an organizations that monitor frauds.
some times the names come hyphenated there is nothing
anyone can do. I am not using the last name as unique at
all. I am using a soundex function to check on the client
name ([LastName] & ", " & [FirstName]) to see if we had
this name before. the clients are from all over the world
and sometimes, especially, clients with hespanic
background, they may have ther name on their BA with hyphen
and on Masters without. I need to be able to match that on
the fly and warn the data entry person on the spot. I hope
that this would clarify the situation.
AL

-----Original Message-----
What do you want to avoid -- not let the client change his/her last name?
not allow the entry of a last name that contains a hyphen? identify the part
of the last name to the left (or right) of the hyphen and then look for a
match?

Please provide us with more details. Note that the use of a last name as a
"unique" identifier has severe limitations for most databases. Can you use a
different identifier to more easily identify a client? or perhaps use the
last name to look up possible clients and choose the one you want?

--
Ken Snell
<MS ACCESS MVP>

I have a code behind a last name field [LastName], that
checks if the database has similar last names. The problem
is that some times an old client(a year or 2 old) comes
back and uses a hyphen(-) in his last name which of course,
will be viewed as different name. How can I avoid this problem?
thanks
Al

.

 

[Ken Snell]

You could use a VBA expression to test for both situations. Assuming that
you have a variable (varLastName) that holds the "incoming" last name, and a
variable (varLastNameDB) that holds the last name that is already in the
database. This is an example of an If .. Then expression that you could use:

If varLastName = varLastNameDB Or varLastName = Replace(varLastNameDB, "-",
"", 1, -1, vbTextCompare) Then
' if true, you have matched the name either with or without hyphens
in the last name
Else
' if false, there is not a match
End If


--
Ken Snell
<MS ACCESS MVP>

The situation is not like that. here is the details. This
db is for a translation co. and every now and then they get
lists of names from an organizations that monitor frauds.
some times the names come hyphenated there is nothing
anyone can do. I am not using the last name as unique at
all. I am using a soundex function to check on the client
name ([LastName] & ", " & [FirstName]) to see if we had
this name before. the clients are from all over the world
and sometimes, especially, clients with hespanic
background, they may have ther name on their BA with hyphen
and on Masters without. I need to be able to match that on
the fly and warn the data entry person on the spot. I hope
that this would clarify the situation.
AL

-----Original Message-----
What do you want to avoid -- not let the client change his/her last name?
not allow the entry of a last name that contains a hyphen? identify the part
of the last name to the left (or right) of the hyphen and then look for a
match?

Please provide us with more details. Note that the use of a last name as a
"unique" identifier has severe limitations for most databases. Can you use a
different identifier to more easily identify a client? or perhaps use the
last name to look up possible clients and choose the one you want?

--
Ken Snell
<MS ACCESS MVP>

I have a code behind a last name field [LastName], that
checks if the database has similar last names. The problem
is that some times an old client(a year or 2 old) comes
back and uses a hyphen(-) in his last name which of course,
will be viewed as different name. How can I avoid this problem?
thanks
Al

.

 

[Marshall Barton]

The situation is not like that. here is the details. This
db is for a translation co. and every now and then they get
lists of names from an organizations that monitor frauds.
some times the names come hyphenated there is nothing
anyone can do. I am not using the last name as unique at
all. I am using a soundex function to check on the client
name ([LastName] & ", " & [FirstName]) to see if we had
this name before. the clients are from all over the world
and sometimes, especially, clients with hespanic
background, they may have ther name on their BA with hyphen
and on Masters without. I need to be able to match that on
the fly and warn the data entry person on the spot.

I thought most soundx functions ignored most punctuation?
If not, why not just change it to treat a hyphen the same
way it uses a space?