Manipulating text string

[genemd]

I am new to Access and have imported data into a table which has a field named employee. All of the data in the employee field is in the last name,first name, middle initial format, e.g., Brown,Thomas R. I want to either create a new field, or update the current employee field in the format of Thomas R Brown. Any help would be appreciated.

[Stingaway]

use a query and the Left, Right, Mid, and/or Instr functions. In your example you would parse the name out based on the location of the comma and the space. The only trick to it is if the data format is ever in a different format than the one you listed.

Examples:

Left([Name], Instr([Name], ",")) will get you the last name (if I have done it correctly)

Mid([Name], Instr([Name], ", "), Instr([Name]," ")) should get you the first name (plus or minus 1 space

Right([Name], 2) - should get you the middle initial give or take a few spaces.

Put them all together in one query field like:

=Left([Name], Instr([Name], ",")) & " " & Right([Name], 2) & " " & Mid([Name], Instr([Name], ", "), Instr([Name]," "))

One caveat: parsing usually takes a bit of trial and error to get it just right. The code above will work partially, but you will have to tweak it to get the positioning just right - and as I said before, the original name field must be in the same order each time. If you have someone like "O'Brien, Michael Shawn" you could get issues...

[alansidman]

Using Stingaway's procedure, I would urge you to put the each porton of the name in separate fields. In this way, you can do sorts on the last name or if necessary only look up on certain last names. It just gives you more flexibility down the road should your requirements change.

Alan

[Stingaway]

Good point Alan. I usually have three fields and then a final field as well for the sort reason you listed...

[genemd]

Thanks for your help in solving my problem.