By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,894 Members | 2,161 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,894 IT Pros & Developers. It's quick & easy.

Parse fields in table

P: 76
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!
Jan 5 '09 #1
Share this Question
Share on Google+
6 Replies

Expert 5K+
P: 8,638
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.
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database
  2. Dim rstParseNames As DAO.Recordset
  4. Set MyDB = CurrentDb
  5. Set rstParseNames = MyDB.OpenRecordset("tblNames", dbOpenDynaset)
  7. With rstParseNames
  8.   If Not .BOF And Not .EOF Then     'has at least 1 Record
  9.     Do While Not .EOF
  10.       If InStr(![UserName], " ") > 0 Then
  11.         .Edit
  12.           ![First] = Left$(![UserName], InStr(![UserName], " ") - 1)
  13.           ![Last] = Mid$(![UserName], InStr(![UserName], " ") + 1)
  14.         .Update
  15.       End If
  16.       .MoveNext
  17.     Loop
  18.   Else
  19.     'fall through
  20.   End If
  21. End With
  23. rstParseNames.Close
  24. Set rstParseNames = Nothing
Jan 6 '09 #2

P: 76

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.
Jan 6 '09 #3

Expert 2.5K+
P: 3,532
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)>
Jan 6 '09 #4

P: 76
Thanks guys for the replies.................
I love that Cardinal Rule, not so much a fan of cleaning up other people's crap!!!
Thanks again!!!
Jan 6 '09 #5

Expert 5K+
P: 8,638
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!
Jan 6 '09 #6

Expert Mod 15k+
P: 31,491
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.
Jan 6 '09 #7

Post your reply

Sign in to post your reply or Sign up for a free account.