Splitting name

[John]

Hi

How can I spilt a name field into surname and forenames in access 200 which
does not have revinstr function as far as I am aware?

Thanks

Regards

 

[Lou]

Hi

How can I spilt a name field into surname and forenames in access 200 which
does not have revinstr function as far as I am aware?

Thanks

Regards

This usually requires multiple passes of the data.

Pass 1: Eliminate leading and trailing spaces.

Update tblContacts
set FullName = ltrim( rtrim( FullName ) );

Pass 2: Repeatedly eliminate double spaces.

update tblContacts
set FullName = left( FullName, instr( FullName, ' ' ) -1 ) +
mid( FullName, instr( FullName, ' ' ) + 1
where instr( FullName, ' ' ) > 0 ;

Pass 3: Split the first name from the last name at the comma.

Update tblContacts
set FirstName = Mid( FullName, instr( FullName, ', ' ) + 2 ),
LastName = left( FullName, instr( FullName, ',' ) - 1 )
WHERE instr( FullName, ',' ) > 0;

Pass 4: Split the middle name from the first name at the space.

Update tblContacts
set MiddleName = Mid( FirstName, instr( FullName, ' ' ) + 1 )
FirstName = left( FirstNameName, instr( FirstName, ' ' ) - 1 )
WHERE instr( FirstName, ' ' ) > 0;

These steps are incomplete if your names include professional titles,
academic credentials, personal titles or personal suffixes such as
"DR.", ",PhD", "Mrs." and "Jr.".

However, the same methodology is used to isolate these components of
the name.

 

[Tom Wickerath]

Hi John,

The function you are thinking of is called InStrRev (not revinstr). This
function was first added in Access 2000, but it is not present in Access 97.

As Lou mentions, you may need to do some initial cleanup work, to eliminate
leading and/or trailing spaces or double spaces, if present. Take a look at
page 6 of a Word document that I call "Access Links.doc" for additional
methods of splitting name data. You are welcome to download a zipped copy
from my web site:

http://www.accessmvp.com/TWickerath/


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________