Identify the FIRST NAME and LAST NAME 1

[Access2007]

Hi, anybody here can help me with my simple problem? how can I get the FIRST NAME and LAST NAME?

james dy
larry king
billy boy gates

[FIRST NAME] [LAST NAME]
mc james dy
larry king
billy boy gates

Your help is very much appreciated. Big THANKS

 

[dhookom]

There isn't a 100% clean method for doing this. There are string functions like:
Instr()
Left()
Right()
Mid()
InStrRev()
Len()

There are some last names with a space like Van Fossen.

You can open any module window and find these in Help. There are also lots of solutions/sample code on the web for parsing last and first names from a single string.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[KornGeek]

Unfortunately, the best way to this is to store them in separate fields from the beginning.

As dhookom stated, there is no 100% clean way to do this.

For example:
Billy Bob Thornton
Eddie Van Halen

How can you create an algorithm to reliably know how to parse these? Even if you created a database of names to know that Bob is typically a first name and Van is typically part of a last name, that is still not 100% reliable. Also, I went to school with a girl with the last name of "Lopez de Nava". Even if you examined the last two "words" of the name, you still would not have her full last name.

If data quality is important, you (or somebody lower on the totem pole) will have to bite the bullet and manually review the data.

 

[Access2007]

Thanks a lot KornGeek! ! !

Keep up the good work..

 

[KornGeek]

Thanks,
I appreciate the kudos, but dhookum is the one who should get the credit. He answered hours before I did (and minutes after the original post) with correct and useful information. I just seconded the information and elaborated a bit.

Good job (as usual), Duane!