Code to separate last name from full name field

[Cathy C]

I imported an Excel workbook with 42 worksheets to Access with the first field as the full name. Although it was in last name order in Excel, it is in some sort of random order in Access. There's no consistency with regard to the use of titles (Mr., Ms., Dr. & Mrs., etc.), and there are a few double names and Jr.s or Ph.D., etc. I can separate those out manually, but it would be helpful to be able to try to isolate the last names (last word following space) so I can sort thereby. Appreciate any assistance.

Thanks,
Cathy

[mdmackillop]

Create a function and call it in a query


Option Compare Database
Function LastName(Data As String)
LastName = Split(Data)(UBound(Split(Data)))
End Function



SELECT Contacts.Name, lastname([name]) AS [Last]
FROM Contacts;

[mdmackillop]

If the Suffix will always contain ".", you can build in the option to return the previous "word"

Function LastName(Data As String)
LastName = Split(Data)(UBound(Split(Data)))
If InStr(1, LastName, ".") > 0 Then
LastName = Split(Data)(UBound(Split(Data)) - 1)
End If
End Function

[mdmackillop]

Forgot to say
Welcome to VBAX!

[Cathy C]

Thank you very much! It's a pleasure to be here - sort of! I googled and found the most excellent code for combining multiple sheets in a workbook which was most helpful. I must admit I'm not sure how to call the macro from the query. I'm using Access 2007 and am not sure how to get there from here.

Thank you for your speedy reply. Hope I can figure out what you're telling me.

Cathy C

[mdmackillop]

Look at the sample in Post 3 for calling the function.