How to swap first and last names in MS Access

Question

I have an Access database where a column of fields contain names where the first name is first and the last name is last. Id like to be able to swap then around so they are Last name first, first name last. For example, I'd like

Bob Smith

to become

Smith, Bob

Does anyone know if this can be done in Access?

Thanks

Answer 1

Depends on how you want to do it. I'd use a function rather than writing convoluted SQL. Here's a function that would do it:

  Public Function SwapNames(ByVal varOriginalName As Variant) As Variant
    Dim strOriginalName As String
    Dim lngLastNameStart As Long
    Dim strLastName As String
    Dim strFirstName As String

    If IsNull(varOriginalName) Or InStr(varOriginalName, " ") = 0 Then
       SwapNames = varOriginalName
    Else
       strOriginalName = varOriginalName
       lngLastNameStart = InStrRev(strOriginalName, " ") + 1
       strLastName = Mid(strOriginalName, lngLastNameStart)
       strFirstName = Left(strOriginalName, lngLastNameStart - 2)
       SwapNames = strLastName & ", " & strFirstName
    End If
  End Function

This returns these values:

  ?SwapNames(Null)
  Null
  ?SwapNames("Fenton")
  Fenton
  ?SwapNames("David Fenton")
  Fenton, David
  ?SwapNames("David W. Fenton")
  Fenton, David W.

...and you'd just use it in your SQL thus:

  UPDATE tblPerson
  SET tblPerson.FullName = SwapNames(tblPerson.FullName)

Now, if you want to do it in SQL only, it's more complicated and really messy. If you want that, just ask, and I'll give it a shot.

Answer 2

Can you create another field that runs a query on access and edits the string? I'm not terribly familiar with Access. I know you can do it in Excel. And maybe that's an option. Export the data to excel, edit the string with formulas then update the Access table to have a field for both first and last name.