VB Name Change

[Hendy]

I have a table of Authors List First Name Surname, I want to use VB to change the authors to surname, first name. I know there will be some exception and will have to change and check the data but can anyone help me to sort out the majority.

Thanks
Mark [sig][/sig]

 

[DougP]

Its best to put the first name in its own field and surname in a different field not both in the same.
If it was done that way then its easy to switch them around when you create your report or what ever else you want to do.
Also if you are searching for all the 'Smiths' the search will go faster. [sig]<p>DougP, MCP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br> Ask me how Bar-codes can help you be more productive.[/sig]

 

[MoGryph]

' Okay, I created a table, with the table name being &quot;tblNames&quot;, and 3 fields, FullName, LName, and FName.
' I then put a bunch of test names in the FullName field, in format &quot;FirstName LastName&quot;.
' The following code then populates the Last and First name fields.
' NOTE- Common programming mistake: Do not give ANY fields, or variables, a &quot;restricted&quot; name.
' Some instances of this include &quot;Name&quot;, &quot;Date&quot;, &quot;String&quot;.
' If you name a field, table, or variable (or anything else, for that matter) you'll have unpredictable
' results, and sometimes problems referenceing the data.
' One thing I did not take into account, is Middle Initials, or titles, like Dr., Mr., Mrs., etc.
Sub FixNames()
Dim dbs As Database
Dim rst As Recordset
Dim lname As String
Dim fname As String
Dim fullname As String
Dim i As Integer

Set dbs = CurrentDb
' Set the recordset to table &quot;tblNames&quot;
Set rst = dbs.OpenRecordset(&quot;tblNames&quot
rst.MoveFirst
While Not rst.EOF
' Set temp &quot;fullname&quot; variable to databases' FullName variable
fullname = rst!fullname
i = InStr(fullname, &quot; &quot
fname = Mid(fullname, 1, i - 1)
lname = Mid(fullname, i + 1)
' Tell Access we're going to edit the record now.
rst.Edit
' Set the records' FName and LName fields to the fName and LName variables
rst!fname = fname
rst!lname = lname
' Set the Fullname field of the record, to the format &quot;LastName, FirstName&quot;
rst!fullname = lname & &quot;, &quot; & fname
' Update the record
rst.Update
' Move to the next record
rst.MoveNext
Wend
' Release the object references.
Set rst = Nothing
Set dbs = Nothing
End Sub