How to extract portion of text

[Frank Merlin]

Hi,

I have managed to extract first name, middle initial and last name into
individual word in query from a person's name, from there I retreive the
first character of each separated word to be used later in the database, the
problem is, I am facing with overseas clients with their name made up by four
group of characters.

e.g. [Name] = "Thomas Lee Jones Peterson" extract to
"Thomas"
"Lee"
"Jones"
"Peterson"

my query already cater for clients with "normal" name like most of us with
or without middle initial, but I dun know how to seperate the "Peterson" like
example above, can any experts out there please help out?

My existing query as follow:

FirstPart: Left([Name],InStr([Name]," ")-1)


SecondPart: Trim(Mid([name],InStr(1,[name]," ")+1,IIf(InStr(InStr(1,[name],"
")+1,[name]," ")=0,0,InStr(InStr(1,[name]," ")+1,[name]," ")-InStr(1,[name],"
"))))


ThirdtPart: IIf(InStr(InStr([name]," ")+1,[name],"
")<>0,Right([name],Len([name])-InStr(InStr([name]," ")+1,[name],"
")),Right([name],Len([name])-InStr([name]," ")))


FourthPart: ?????????????

Thank you in advance.

 

[KARL DEWEY]

What are you going to try when someone has 15 names? I would recommend not
going for four parts of the name and stick with only three.
There are folks from the south the have double first names like --
Sue Ann
Billy Bob
Joe Bob
You need to decide if you will place the extra names in the first or middle.
Another problem that will come up is split last names like --
De La Hoya
Le Shaw
Van Johnson

 

[Frank Merlin]

Thanks for the replay, for the time being, cater for spliting into 4 names is
more than enought for me, I really need help to solve for the 4th part query.

If anyone knows how to split names into individual name despite the lenght ,
I will be eternally greatfull.

What are you going to try when someone has 15 names? I would recommend not
going for four parts of the name and stick with only three.
There are folks from the south the have double first names like --
Sue Ann
Billy Bob
Joe Bob
You need to decide if you will place the extra names in the first or middle.
Another problem that will come up is split last names like --
De La Hoya
Le Shaw
Van Johnson

--
Build a little, test a little.

Hi,

I have managed to extract first name, middle initial and last name into
individual word in query from a person's name, from there I retreive the
first character of each separated word to be used later in the database, the
problem is, I am facing with overseas clients with their name made up by four
group of characters.

e.g. [Name] = "Thomas Lee Jones Peterson" extract to
"Thomas"
"Lee"
"Jones"
"Peterson"

my query already cater for clients with "normal" name like most of us with
or without middle initial, but I dun know how to seperate the "Peterson" like
example above, can any experts out there please help out?

My existing query as follow:

FirstPart: Left([Name],InStr([Name]," ")-1)


SecondPart: Trim(Mid([name],InStr(1,[name]," ")+1,IIf(InStr(InStr(1,[name],"
")+1,[name]," ")=0,0,InStr(InStr(1,[name]," ")+1,[name]," ")-InStr(1,[name],"
"))))


ThirdtPart: IIf(InStr(InStr([name]," ")+1,[name],"
")<>0,Right([name],Len([name])-InStr(InStr([name]," ")+1,[name],"
")),Right([name],Len([name])-InStr([name]," ")))


FourthPart: ?????????????

Thank you in advance.

 

[KARL DEWEY]

If by fourth part you mean the last name then this --
Mid([Name], InStrRev([Name], " ") +1)

--
Build a little, test a little.

Thanks for the replay, for the time being, cater for spliting into 4 names is
more than enought for me, I really need help to solve for the 4th part query.

If anyone knows how to split names into individual name despite the lenght ,
I will be eternally greatfull.

What are you going to try when someone has 15 names? I would recommend not
going for four parts of the name and stick with only three.
There are folks from the south the have double first names like --
Sue Ann
Billy Bob
Joe Bob
You need to decide if you will place the extra names in the first or middle.
Another problem that will come up is split last names like --
De La Hoya
Le Shaw
Van Johnson

--
Build a little, test a little.

Hi,

I have managed to extract first name, middle initial and last name into
individual word in query from a person's name, from there I retreive the
first character of each separated word to be used later in the database, the
problem is, I am facing with overseas clients with their name made up by four
group of characters.

e.g. [Name] = "Thomas Lee Jones Peterson" extract to
"Thomas"
"Lee"
"Jones"
"Peterson"

my query already cater for clients with "normal" name like most of us with
or without middle initial, but I dun know how to seperate the "Peterson" like
example above, can any experts out there please help out?

My existing query as follow:

FirstPart: Left([Name],InStr([Name]," ")-1)


SecondPart: Trim(Mid([name],InStr(1,[name]," ")+1,IIf(InStr(InStr(1,[name],"
")+1,[name]," ")=0,0,InStr(InStr(1,[name]," ")+1,[name]," ")-InStr(1,[name],"
"))))


ThirdtPart: IIf(InStr(InStr([name]," ")+1,[name],"
")<>0,Right([name],Len([name])-InStr(InStr([name]," ")+1,[name],"
")),Right([name],Len([name])-InStr([name]," ")))


FourthPart: ?????????????

Thank you in advance.

 

[Frank Merlin]

Thanks Karl, you are a big help to me, I am new to Access, this site has
provided me with a GREAT help with all the expert opinions.

 

[Dale Fye]

Answered a question similar to this the other day. Try using this function:

Public Function fnSplitPart(SomeValue As Variant, Delimiter As String, Part
As Integer) As Variant

Dim MyArray() As String
Dim strValue As String

If IsNull(SomeValue) Then
fnSplitPart = Null
Else
strValue = CStr(SomeValue)
MyArray = Split(strValue, Delimiter)
If UBound(MyArray) + 1 < Part Then
fnSplitPart = Null
Else
fnSplitPart = MyArray(Part - 1)
End If
End If

End Function

Then, in your query you can use:

Col1:fnSplitPart([FieldName], ".", 1)
Col2:fnSplitPart([FieldName], ".", 2)


--
----
HTH
Dale

If by fourth part you mean the last name then this --
Mid([Name], InStrRev([Name], " ") +1)

--
Build a little, test a little.

Thanks for the replay, for the time being, cater for spliting into 4 names is
more than enought for me, I really need help to solve for the 4th part query.

If anyone knows how to split names into individual name despite the lenght ,
I will be eternally greatfull.

What are you going to try when someone has 15 names? I would recommend not
going for four parts of the name and stick with only three.
There are folks from the south the have double first names like --
Sue Ann
Billy Bob
Joe Bob
You need to decide if you will place the extra names in the first or middle.
Another problem that will come up is split last names like --
De La Hoya
Le Shaw
Van Johnson

--
Build a little, test a little.


:

Hi,

I have managed to extract first name, middle initial and last name into
individual word in query from a person's name, from there I retreive the
first character of each separated word to be used later in the database, the
problem is, I am facing with overseas clients with their name made up by four
group of characters.

e.g. [Name] = "Thomas Lee Jones Peterson" extract to
"Thomas"
"Lee"
"Jones"
"Peterson"

my query already cater for clients with "normal" name like most of us with
or without middle initial, but I dun know how to seperate the "Peterson" like
example above, can any experts out there please help out?

My existing query as follow:

FirstPart: Left([Name],InStr([Name]," ")-1)


SecondPart: Trim(Mid([name],InStr(1,[name]," ")+1,IIf(InStr(InStr(1,[name],"
")+1,[name]," ")=0,0,InStr(InStr(1,[name]," ")+1,[name]," ")-InStr(1,[name],"
"))))


ThirdtPart: IIf(InStr(InStr([name]," ")+1,[name],"
")<>0,Right([name],Len([name])-InStr(InStr([name]," ")+1,[name],"
")),Right([name],Len([name])-InStr([name]," ")))


FourthPart: ?????????????

Thank you in advance.