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

Seperate Name field into three seperate fields

P: 4
I am working with an ACCESS query that has a field called PR_EMP_NAME. This field contains an employee’s last name, first name and middle name. I need to separate this field into three separate fields. I have been able to separate the field into the last and middle names but can’t figure out how to get the first name which is in between the last and middle names. All names are separated by a space. I am using the following to obtain the Last and Middle names. Any ideas how I can get the first name?
Expand|Select|Wrap|Line Numbers
  1. LastName: Trim(Left([PR_EMP_NAME],InStr(1,[PR_EMP_NAME]," ")))
Expand|Select|Wrap|Line Numbers
  1. MiddleName: Right([personnel]![PR_EMP_NAME],Len([personnel]![PR_EMP_NAME])-InStrRev([personnel]![PR_EMP_NAME]," "))
Expand|Select|Wrap|Line Numbers
  1. FirstName ?????
May 6 '10 #1

✓ answered by gershwyn

Try this:
Expand|Select|Wrap|Line Numbers
  1. FirstName: Trim(Mid([PR_EMP_NAME],InStr(1,[PR_EMP_NAME]," "),InStrRev([PR_EMP_NAME]," ")-InStr(1,[PR_EMP_NAME]," ")))
This will consider everything between the first space and the last space in the name to be the "middle" name. That should work as you've described your source data; if it doesn't then please post some examples for us to work with and we can modify the function a bit.

Hope that was helpful, and welcome to Bytes. :)

Share this Question
Share on Google+
2 Replies


100+
P: 122
Try this:
Expand|Select|Wrap|Line Numbers
  1. FirstName: Trim(Mid([PR_EMP_NAME],InStr(1,[PR_EMP_NAME]," "),InStrRev([PR_EMP_NAME]," ")-InStr(1,[PR_EMP_NAME]," ")))
This will consider everything between the first space and the last space in the name to be the "middle" name. That should work as you've described your source data; if it doesn't then please post some examples for us to work with and we can modify the function a bit.

Hope that was helpful, and welcome to Bytes. :)
May 6 '10 #2

P: 4
Thanks this did it.
May 6 '10 #3

Post your reply

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