splitting a name field

[Lorien2733]

I have a last name field that includes a suffix (Jr, Sr, III etc) if
applicable. Not all names have a suffix. How can I pull the suffix off, if
it exists, into a separate field? This seems like it should be such a simple
thing but I'm drawing a complete blank.

TIA,
Lynne

 

[kingston via AccessMonster.com]

To do this in a very safe manner, I think you'll need to compile a list of
possible suffixes for exact comparison. Then, either in code or through
queries, look for names ending in " suffix". For example, if you do this in
a query and want to return the name without the suffix:

NewName: Left(OldName, Len(OldName)-3) )
With this criteria for OldName - Like "* Jr" Or Like "* Sr"...

You'd need different queries for suffixes of different length. Otherwise,
you can scan each entry in code for the different endings. HTH.

 

[Jeff Boyce]

Lynne

Is there anything in common about the LastNames that do include a suffix?
For example, is there always a comma following the LastName and preceding
the Suffix? Then, is there always a space between LastName and Suffix?
Only one of these (comma or space) or always both (comma and space).

If you have that you could use the Instr() function to look for where that
happens, then take everything past that point as Suffix.

HOWEVER, if your LastNames includes names that have the same character(s) as
you are using for finding where the Suffix starts, Access won't be able to
tell which one marks the start.

I think you'll find that USB (using someone's brain) will be needed after
Access does the best it can.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Albert D. Kallal]

This can be a considerable complex task in terms of software development.

Much of how difficult this task will be is how rigid, or how well the field
name is formatted.

St. Pierre

The above, is the last name = St.

and the suffix = Pierre

As you can see, this rapidly becomes a difficult task. In fact, if the
computer could easily figure out how to do this, the computer likely could
also take over your job.

So, if the suffix IS ALWAYS followed by a "," (comma), and it is consistent
then you can accomplish your goal with relative ease. If the software you
desire has to look at the name, and figure out the suffix, then you are big
trouble!!

if *always* the suffix if a "comma + suffix", then it is relative easy to do
this.

eg:

Smith, Sr.
Kallal, Jr.

etc...


The follow code would do accomplish this task

Public Sub GrabSuffix()


Dim strSql As String
Dim rst As DAO.Recordset
Dim intCommaLoc As Integer


strSql = "select * from tblcustomers where LastName is not null"

Set rst = CurrentDb.OpenRecordset(strSql)

Do While rst.EOF = False

' check if last name as a "," in it
intCommaLoc = InStrRev(rst!LastName, ",")

If intCommaLoc > 0 Then
' we have the postion of the comma. Move this to our new suffix
field
' but, skip the comma
rst.Edit
rst!Suffix = Trim(Mid(rst!LastName, intCommaLoc + 1))
rst!LastName = Left(rst!LastName, intCommaLoc - 1)
rst.Update
End If

Loop

rst.Close

MsgBox "conversion complete"

End Sub

Please note you should only attempted this code on a copy of the data until
your are VERY much satisfied with the results.
The above code is also "air" code, and un-tested (I just typed it as I am
typing now...so, it is off the top of my head, but looks very close to what
you need).