Cutting down someone's name

  • Thread starter Thread starter Russell Farr
  • Start date Start date
R

Russell Farr

Hi

I hope someone can help. I have a field in a table
called 'Manager Name' - it is a single field, not split
into First Name or Surname (due to source of information -
external feed).

When generating emails from the database, I want the
template text to refer to the manager's first name only -
e.g. "Dear Joe" - at the moment it says "Dear Joe Bloggs".

Does anyone know of any function or anything that I can
use to cut the text down to just the characters before the
Space character (and by doing so cutting out the surname)?

Thanks in advance for your help.

Russell
 
Use Instr and Left to get the value

Trim(Left([Manager Name],Instr([Manager Name]," ")))

Just a note of warning: This could give you a problem when you have a name like
Mary Anne Simmons, in that it would return only Mary. And I do know some people
that would be upset if you truncated that to only Mary.
 
Hi,
By using a combination of Left and InStr functions you can do what you want:

Left([Manager Name],InStr(1,[Manager Name]," ",1) -1)

The above can be used in a query or in VBA. Look up the functions in help
to understand what they do.
 
Russell Farr said:
Hi

I hope someone can help. I have a field in a table
called 'Manager Name' - it is a single field, not split
into First Name or Surname (due to source of information -
external feed).

When generating emails from the database, I want the
template text to refer to the manager's first name only -
e.g. "Dear Joe" - at the moment it says "Dear Joe Bloggs".

Does anyone know of any function or anything that I can
use to cut the text down to just the characters before the
Space character (and by doing so cutting out the surname)?
Click to expand...

Use the instr command to find the position of the sace and the left command
to extract the first name.
Left(strTest, InStr(1, strTest, " ") - 1).
To extract the surname use the right command.
Example below.

Andy

Sub test()

Dim strTest As String
Dim strLeft As String
Dim strRight As String

strTest = "Joe Bloggs"
strLeft = Left(strTest, InStr(1, strTest, " ") - 1)
strRight = Right(strTest, Len(strTest) - InStr(1, strTest, " "))

MsgBox strRight

End Sub
 
Junk the single field idea. It is just not possible to determine a person's
forname & surname programatically, from a single field. If you don't believe
me, show me the code to reliably extract the forname & surname from the
following names :-)

John Smith
John A Smith
John Van Der Smith
John Smith III
Mr John Smith

I always have four fields: title (Mr, Mrs etc), forname, midname (middle
name(s) or initial(s)), surname. Then you can display those fields together
when you need the full name on an email etc.

HTH,
TC
 
TC said:
Junk the single field idea. It is just not possible to determine a person's
forname & surname programatically, from a single field. If you don't believe
me, show me the code to reliably extract the forname & surname from the
following names :-)

John Smith
John A Smith
John Van Der Smith
John Smith III
Mr John Smith

I always have four fields: title (Mr, Mrs etc), forname, midname (middle
name(s) or initial(s)), surname. Then you can display those fields together
when you need the full name on an email etc.
Click to expand...

....Still not convinced? Then what is my surname?
 
Bas Cost Budde said:
...Still not convinced? Then what is my surname?
Click to expand...

Sorry, I don't understand what you're getting at. I say it is difficult or
impossible to write code that reliably extracts forname & surname from a
free-form name field. Are you agreeing with that, or disagreeing? If you're
agreeing - then we agree! If you're disagreeing, how does your question
("what is my surname?") add to the argument?

Cheers,
TC
 
I meant to add to that.

Titles like Mr can be overcome (it is a pain already) but how can one
distinguish between a second first name (huh) and a double family name?
Sorry, I don't understand what you're getting at. I say it is difficult or
impossible to write code that reliably extracts forname & surname from a
free-form name field. Are you agreeing with that, or disagreeing? If you're
agreeing - then we agree! If you're disagreeing, how does your question
("what is my surname?") add to the argument?
Click to expand...

So, I am agreeing. Too much of the meaning of my addition would have
been in tone of voice, I miss that often when posting.

Regards.
 
Bas Cost Budde said:
Titles like Mr can be overcome (it is a pain already) but how can one
distinguish between a second first name (huh) and a double family name?
Click to expand...

I have done this by having a table of titles and using that as a lookup
By having a table of exceptions.
The database runs through the import data and seperates the obvious. Where a
name has 3 or more words in it, assign a placeholder (eg Forename1, Surname1
etc) and save the entire name and placeholders to a table.
Once all the data has been imported, call for manual intervention in
deciding what is a forename and what is a surname. Then, once the user has
set the forname and surnames, do a find and replace on the placeholders.

Andy
 
Andy said:
I have done this by having a table of titles and using that as a lookup
By having a table of exceptions.
The database runs through the import data and seperates the obvious. Where a
name has 3 or more words in it, assign a placeholder (eg Forename1, Surname1
etc) and save the entire name and placeholders to a table.
Once all the data has been imported, call for manual intervention in
deciding what is a forename and what is a surname. Then, once the user has
set the forname and surnames, do a find and replace on the placeholders.
Click to expand...
That is a very thorough approach and deserves recommendation!
 
Back
Top