Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

share|improve this question
2  
Let this be a lesson to separate names into distinct first and last name fields. –  ceejayoz Feb 13 '10 at 16:28
    
Tell me about it :( –  JimDel Feb 13 '10 at 16:32
    
I'm sure there is some fancy sql, vb-script possibility to do it from within access. But have you considered just exporting the data and perform the manipulation in your script language of choice? Then just insert/update the data again. –  Nifle Feb 13 '10 at 16:54

2 Answers 2

up vote 3 down vote accepted

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.

share|improve this answer
    
Thanks David! That's great. –  JimDel Feb 14 '10 at 16:24

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.