Parse fields in table

[artemetis]

Howdy!
I've got a table with some contact information.
uid, emailaddy, username

The user name data is inconsistent.... some names are as Bill Jones, some are B.Jones and some are the email address as well.

I need to parse through the fields and where there is a space between the names, break them out into two separate fields.

I'm stumped!

Any direction would be greatly appreaciated!
Thanks!

[ADezii]

@artemetis
Assuming your Table Name is tblNames, I know not very original, the following code will parse any Value in the userName Field that has a space in it, and Update two Fields named First and Last. But first:

1. If your Table Name is not tblNames, change it in the code.
2. Add 2 Fields to your Table and name them First and Last. Should you name them differently, adjust the code accordingly.
3. Download the Attachment to get a visual cue as to what is going on.
4. Any questions concerning the code, feel free to ask.

Dim MyDB As DAO.Database
Dim rstParseNames As DAO.Recordset
 
Set MyDB = CurrentDb
Set rstParseNames = MyDB.OpenRecordset("tblNames", dbOpenDynaset)
 
With rstParseNames
  If Not .BOF And Not .EOF Then     'has at least 1 Record
    Do While Not .EOF
      If InStr(![UserName], " ") > 0 Then
        .Edit
          ![First] = Left$(![UserName], InStr(![UserName], " ") - 1)
          ![Last] = Mid$(![UserName], InStr(![UserName], " ") + 1)
        .Update
      End If
      .MoveNext
    Loop
  Else
    'fall through
  End If
End With
 
rstParseNames.Close
Set rstParseNames = Nothing

[artemetis]

Thanks!

As it turns out, the table is slightly more complicated.

There is not only first and last name in there, but Middle initials, and some other crap, which at this point, i'm thinking is impossible to clean up.

[missinglinq]

We see questions like this, both here and elsewhere, over and over and over again! If the original data is as simple as FirstName LastName, or even FirstName MiddleInitial LastName, code can easily be written to handle the parsing. But it's usually not that simple when it comes to names!

It's usually things like

FirstName LastName

FirstName MiddleInitial LastName

FirstName LastName Qualifier (Sr, Jr, III, etc)

Mr/Mrs FirstName LastName

Mr/Mrs FirstName MiddleInitial LastName

FirstName MiddleInitial LastName Title (M.D. Esq. CEO, etc)

and so forth, including the two part LastNames, such as van Allen and that guy that Bush has been chasing for so long! And so, in the end, you usually end up having to visually inspecting all records and making adjustments accordingly.

If the majority of names are FirstName LastName, or FirstName MiddleInitial LastName, using code to start the parsing process and then inspecting the data and making corrections may be worth the trouble, especially if you're talking about a lot of records. It all depends.

BTW, the other similar PIA we see involves parsing addresses! Years ago I moonlighted, doing data entry for one of the largest banks in the world. They'd bought out another regional bank and were trying to convert/format the customer addresses to fall in line with their own system's formatting. After a team of 6 programmers spent 4 months trying to figure out how to do this "automatically" they finally bit the bullet and hired data input personnel to scan the data and make the corrections.

The bottom line is to remember the Cardinal Rule of Relational Databases: One Piece of Data/One Field!

Good Luck and Welcome to Bytes!

Linq ;0)>

[artemetis]

Hahahahahahhaha!
Thanks guys for the replies.................
I love that Cardinal Rule, not so much a fan of cleaning up other people's crap!!!
Thanks again!!!

[ADezii]

@artemetis
I responded to a similar Thread not that long ago involving names which may/may not have Prefixes, Suffixes, First, Last, Titles, etc. As soon as I can retrieve it, I'll post the Link to it. It will definately point you in the right direction, but as Linq stated, there is no foolproof Method to accomplish this.
Got it!
http://bytes.com/topic/access/answer...unction-script

[NeoPa]

@artemetis
ADezii's link will probably get you off to a good start.

As for handling 'other people's crap', we always hope they can pass us something clean and tidy, but don't rely too heavily on that. In the real world you're lucky if you get too much passed on that doesn't need a fair amount of 'cleaning out' ;D.