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

Selecting Part of a Name field

P: 2
Hello

I am trying to select the first name and surname from a name field where the name is in the form of;

eg. Mrs Marilyn Payne
Mrs Mary Swanton
Ms EM Lomas
Lt Col R Cartwright

I need to break the name down into forename and surname separately so that I can link to another database where forename and surame are seperate.

I have attempted using the InStr function in access to locate the first space (" ") but cannot seem to get it to work.

Mid([Name],InStr(1,[Name]," ")+1,InStr(InStr(1,[Name]," ")+1,[Name]," ")-InStr(1,[Name]," ")-1))

The expression above is attempting to return the characters that are located between the first and second spaces in the Name field.

Any assistance would be greatly appreciated.

N
Aug 1 '07 #1
Share this Question
Share on Google+
4 Replies


FishVal
Expert 2.5K+
P: 2,653
Hello

I am trying to select the first name and surname from a name field where the name is in the form of;

eg. Mrs Marilyn Payne
Mrs Mary Swanton
Ms EM Lomas
Lt Col R Cartwright

I need to break the name down into forename and surname separately so that I can link to another database where forename and surame are seperate.

I have attempted using the InStr function in access to locate the first space (" ") but cannot seem to get it to work.

Mid([Name],InStr(1,[Name]," ")+1,InStr(InStr(1,[Name]," ")+1,[Name]," ")-InStr(1,[Name]," ")-1))

The expression above is attempting to return the characters that are located between the first and second spaces in the Name field.

Any assistance would be greatly appreciated.

N
Hi!

To return the characters that are located between the first and second spaces in the Name field you can use Split function which returns array of substrings.
Expand|Select|Wrap|Line Numbers
  1. Split([Name]," ")(1)
  2.  
BTW its better to store salutation, first and last name in separate fields. Reasons are clear I think.
Aug 1 '07 #2

missinglinq
Expert 2.5K+
P: 3,532
Sorry to tell you but attempting to do this in code is pretty much doomed to failurel! The problem, of course, is that the full name field youíre trying to parse has no fixed format! Successfully parsing a string requires having a fixed number of occurrences of a given character (a space in this case) in each string. Just look at the couple of examples youíve posted:

Mrs Marilyn Payne 2 spaces
Mrs Mary Swanton 2 spaces
Ms EM Lomas 2 spaces 2 spaces
Lt Col R Cartwright 3 spaces

And thatís not even considering names like

Mrs Maria von Trapp 3 spaces
Mr. Oscar De La Hoya 4 spaces
Maj. Charles Emerson Winchester III 4 spaces

The possibilities are almost endless! Any scheme to parse the full name is going to have failures. You can do some preliminary parsing, which would hopefully reduce the amount of human interaction needed, but someone will then need to go in and look over all of the names and make corrections where necessary! Donít know what kind of form youíre using, but a datasheet view form would be easiest for doing the corrections. If the data isnít available in this format, you could always have Access do one up via the Forms Wizard.

Are all names preceeded with the title (i.e. Mr MS Mrs)? Let me know and Iíll post some expressions for doing some of the parsing.

Welcome to The Scripts!

Linq ;0)>
Aug 1 '07 #3

P: 2
Hi Linq

Yes most of the names are Mr, Mrs, or Ms and have 2 spaces between the Title, Forename and surname. I can manually parse the Lt Col so i mainly interested in the Mr, Mrs & MS

Cheers

N


Sorry to tell you but attempting to do this in code is pretty much doomed to failurel! The problem, of course, is that the full name field youíre trying to parse has no fixed format! Successfully parsing a string requires having a fixed number of occurrences of a given character (a space in this case) in each string. Just look at the couple of examples youíve posted:

Mrs Marilyn Payne 2 spaces
Mrs Mary Swanton 2 spaces
Ms EM Lomas 2 spaces 2 spaces
Lt Col R Cartwright 3 spaces

And thatís not even considering names like

Mrs Maria von Trapp 3 spaces
Mr. Oscar De La Hoya 4 spaces
Maj. Charles Emerson Winchester III 4 spaces

The possibilities are almost endless! Any scheme to parse the full name is going to have failures. You can do some preliminary parsing, which would hopefully reduce the amount of human interaction needed, but someone will then need to go in and look over all of the names and make corrections where necessary! Donít know what kind of form youíre using, but a datasheet view form would be easiest for doing the corrections. If the data isnít available in this format, you could always have Access do one up via the Forms Wizard.

Are all names preceeded with the title (i.e. Mr MS Mrs)? Let me know and Iíll ost some expressions for doing some of the parsing.

Welcome to The Scripts!

Linq ;0)>
Aug 1 '07 #4

missinglinq
Expert 2.5K+
P: 3,532
If you manually modify the full names so that they are all formatted

Title - FirstName - LastName

then these formulas will work:

First Name
Expand|Select|Wrap|Line Numbers
  1. FirstNameComponent = Left(Right(FullName,Len(FullName)- instr(FullName," ")),(InStrRev(Right(FullName,Len(FullName)- instr(FullName," ")), " ")-1))
Last Name
Expand|Select|Wrap|Line Numbers
  1. LastNameComponent = Right(FullName, (Len(FullName) - InStrRev(FullName, " ")))
  2.  
Good luck to you!

Linq ;0)>
Aug 1 '07 #5

Post your reply

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