select only first names

  • Thread starter Thread starter ricardozelaya via AccessMonster.com
  • Start date Start date
R

ricardozelaya via AccessMonster.com

If a field contains "John Doe and Jane Tarzan"

How do you extract "John and Jane"

The field is different lengths depending on the size of the names. I need to
write a letter that would say:

Dear John and Jane, ...
 
You need to normalize your data. Please do a search and read the hundreds
of previous posts on first and last name separation and storage. You will
find several posts on how to do it and why you need to store each name
(First, Last, Middle) in a separate field.

Please search the newsgroups for common questions and answers before adding
a new thread to the forum.
 
If a field contains "John Doe and Jane Tarzan"

How do you extract "John and Jane"

The field is different lengths depending on the size of the names. I need to
write a letter that would say:

Dear John and Jane, ...
Click to expand...

You can do this if ALL of the records are in the same format as
indicated above.

If your version of Access supports the Split function, copy and Paste
the below code into a new Module:

Public Function ParseText(TextIn As String, X) As Variant
On Error Resume Next
Dim Var As Variant
Var = Split(TextIn, " ", -1)
ParseText = Var(X)
End Function

In your query, add a new column:

FirstNames:ParseText([CombinedNames],0) & " and " &
ParseText([CombinedNames],3)

*** The above will only work if your combined names are exactly
"John Smith and Mary Jones".

Names such as
"Mary Lou van Houten and M. Jones "
will not.
 
Back
Top