I have a field, fullname that I want to split into 2 fields.
fullname looks like this:
SMITH, ADAM J
DOWNS, RONNY A
SMITH, DERECK
I want to split it into
Smith
Adam
Downs
Ronny
how can I do this?
Surname: Left([fullname], InStr([fullname], ",") - 1
Firstname: Trim(Mid([fullname], InStr([fullname], ",") + 1))
The latter will put "ADAM J" into Firstname; bear in mind that some people
have two-word first names such as "DAISY MAE" or "BOBBI JO". If you want to
ignore their preference and truncate their name, use
Left(Trim(Mid([fullname], InStr([fullname], ",") + 1)),
InStr(Trim(Mid([fullname], InStr([fullname], ",") + 1)), " ") - 1)
--
John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also
http://www.utteraccess.com