Separating First and Last Names

  • Thread starter Thread starter TJones
  • Start date Start date
T

TJones

I have a table which has a "Name" field that contains the
names of our members in the format "LastName, FirstName".
I need to change these to "Firstname Lastname" and make a
new table with that for another database. Can anyone help
with this? Thanks a bunch.

TJones
 
I have a table which has a "Name" field that contains the
names of our members in the format "LastName, FirstName".
I need to change these to "Firstname Lastname" and make a
new table with that for another database. Can anyone help
with this? Thanks a bunch.

TJones
Click to expand...

If you really do have a field named "Name" I would strongly suggest
you change it to something else, perhaps "ClientName" or "CustName".
See the appropriateMicrosoft KnowledgeBase article for your version of
Access:
109312 'Reserved Words in Microsoft Access'
209187 'Acc2000: 'Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'

To answer your question regarding parsing the names into 2 SEPARATE
fields:
IF the names are all in the same order..
LastName Comma Space FirstName
Smith, Frank

LastName:Left([FullName],InStr([FullName],",") -1
FirstName:Mid([FullName],InStr([FullName],",")+2

You would use the above to create 2 separate fields in a make table
query.

Using 2 separate fields, you can easily put the names together in any
order you wish:
= [FirstName] & " " & [LastName]
Frank Smith
= [Lastname] & ", " & [FirstName]
Smith, Frank
= Left([FirstName],1) & ". " & [LastName]
F. Smith
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You can use the Left(), Mid() and InStr() functions to get the names -
if you're POSITIVELY, ABSOLUTELY certain that all the rows of data
have the name in your stated format. E.g.:

SELECT Left([Name], InStr([Name], ",") - 1 As LastName,
[Name]), Mid([Name], InStr([Name], ", ") + 1 As FirstName
FROM ...
WHERE [Name] IS NOT NULL

You'll probably run into some rows that have only the first name or
only the last name - these may cause problems. I'll leave that
solution to you - hint: use more than one query with different WHERE
clauses.

- --
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQDOv0IechKqOuFEgEQITuACdFD+SLJRxSjaizV1CWQisNALgfqcAoM4o
Ye5P2wgcrfA9rW0T2ChyyXeP
=OfYd
-----END PGP SIGNATURE-----
 
Back
Top