Hi GB,
A very easy method to use involves using Excel before importing your data.
From within Excel, use Data > Text to Columns... Then import your table
into Access.
One of my favorite utilities for splitting names in Access is called
"Splitter for Microsoft Access". If you don't mind popping $39 for a copy,
you will have a very good utility that can untangle the messiest name data,
especially if you have a recurring need:
http://www.infoplan.com.au/splitter/
You can also create the new fields in the table and then use an update query
to populate the new fields. The update query would look like this:
Field: LastName
Table: Your tablename
Update To: SplitName([FullName],0)
Field: FirstName
Table: Your tablename
Update To: SplitName([FullName],1)
Field: MiddleInitial
Table: Your tablename
Update To: SplitName([FullName],2)
Create a new standard module. Then copy and paste the following function
into it. This will create a zero-based array named strResult. The On Error
Resume Next statement will prevent the function from choking to a halt if
there is no middle name for a given record.
Public Function SplitName(FullName As String, _
intElement As Integer) As String
On Error Resume Next
Dim strResult() As String
strResult = Split([FullName], Chr(32))
SplitName = strResult(intElement)
End Function
You can also split names using the appropriate combination of various
functions, such as InStr, Left, Mid, etc. directly within the query, but I
think this is a bit harder to debug versus calling an external function.
Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
