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.
