I have an access database with a text field that reflects names: eg.
first
name, last name, ini, prefix - Doe, John, M., Mr.
How can I globally break this field into individual fields such as:
[firstname],[lastname],[ini],[prefix]?
I look forward to your professional assistance.
If you have Access 2000 or newer:
Make sure the data layout is exactly as you show it.
You indicagte it's in FirstName, LastName, etc. order, however your
example shows LastName, FirstName, etc. order.
Let's work with LastName, FirstName order.
If a name if missing, leave it's comma, i.e. Smith, Joseph, , Dr.
Copy and Paste the following into a Module:
Public Function ParseText(TextIn As String, X) As Variant
On Error Resume Next
Dim var As Variant
var = Split(TextIn, ",", -1)
ParseText = var(X)
End Function
===========
Make a back-up table first.
Then paste this SQL into a new Query.
Change YourTable and field names to what ever the actual table name
and field names are.
Update YourTable Set YourTable.[LastName] =
Trim(ParseText([FullName],0)),YourTable.[FirstName] =
Trim(ParseText([FullName],1)),YourTable.[MI] =
Trim(ParseText([FullName],2)),YourTable.[Prefix] =
Trim(ParseText([FullName],3)) Where YourTable.[FullName] Is Not Null;