Seeking help to extratc letters from name fields

[Guest]

Hi all: I have two fields family_name & given_name.I need to extract 2nd, 3rd
& 5th letters of family_name and 2nd & 3rd letters of given_name into text
field called link_key. Where names do not have suffient letters for
extraction then the numeral 2 is used as a replacement. Currently entering
link_key data manually but is there another way?

 

[Guest]

This is to get the letters you need:

Link_key: Mid(family_name,2,2)&Mid(family_name,5,1)&Mid(family_name,2,2)

As far as names that would not fall into this (2 as replacement) use Len()
function in IIf statemet, something like
IIF(Len(family_name)<=2,2,Mid(family_name,2,2)&Mid(family_name,5,1)&Mid(family_name,2,2)).

The best option would be to write a custom function, e.g.

Function Link_Key(strFamilyName as String, strGivenName as String) as String
and place all the variable within this function in case you need to use this
over or the parameters change.

Hope this helps.

Luke

 

[John Vinson]

Hi all: I have two fields family_name & given_name.I need to extract 2nd, 3rd
& 5th letters of family_name and 2nd & 3rd letters of given_name into text
field called link_key. Where names do not have suffient letters for
extraction then the numeral 2 is used as a replacement. Currently entering
link_key data manually but is there another way?

It sounds to me like you're going to a great deal of work to construct
an "intelligent key" which will give you no end of trouble.

Names are not unique. Extracting five letters from names is even LESS
unique. "David Johnson" and "Ravenna Cohasset" will both convert to
AVOHS for example.

If you're looking for a unique link key, an Autonumber will be much
more reliable, not to mention much easier to implement!

John W. Vinson[MVP]

 

[Fresh Prince via AccessMonster.com]

I agree with John Vinson.

If you want to create a key, this is not the ideal way to do it.
Then you even can better make a join of two fields which will have more
chance of being unique.

When you want to be completely sure, just make as John said an Autonumber
ID.

Regarding the solution for your selection of letters, look at the first
answer.