Selecting words from a text field

[Eddie Mason]

Is there a way of selecting words from a text field e.g. [NameField] contains the following text “John Paul Simon Smith” I would like to return the following:
Firstword returns “John”
Midword1 returns “Paul”
Midword2 returns “Simon”
Lastword Returns “Smith”
Can anyone help with this problem?

Regards

Eddie

 

[Mile-O]

Code Repository - GetPart()

 

[Eddie Mason]

Hi Mile-O-Phile,

I've loaded the Function and it works perfectly selecting the first word in the list, but when I ask it to call the second word in the list and there isn’t one it Error # 5 “Invalid Procedure Call or Argument”. The words I want to pick out are all separated by a space and I’m calling as follows:
Name1:GetPart([ClientName],” “,0)
Name2:GetPart([ClientName],” “,1)
Name3:GetPart([ClientName],” “,2)
Name4:GetPart([ClientName],” “,3)
Am I doing something wrong?

Regards

Eddie

 

[Eddie Mason]

Hi Mile-O-Phile,

I've run the query as requested without the error trap and it stops on:
GetPart = Mid(strTemp, intPos + 1, InStr(intPos + 1, strTemp, strDelim) - (intPos + 1))

Is this of any help?

Regards

Eddie

 

[Mile-O]

This will tell you how many spaces in a field - put it in the module with the GetPart function.

Public Function CountSpaces(strText As String) As Integer
    Dim intCounter As Integer
    For intCounter = 1 To Len(strText)
        If Mid(strText, intCounter, 1) = " " Then
            CountSpaces = CountSpaces + 1
        End If
    Next intCounter
End Function

 

[Mile-O]

You'd also need a strucure like this for calling from the query.

Then, for each column, call:

Field1: AssignName(CountSpaces([MyField]), 1)
Field2: AssignName(CountSpaces([MyField]), 2)
Field3: AssignName(CountSpaces([MyField]), 3)
Field4: AssignName(CountSpaces([MyField]), 4)

Public Function AssignName(intSpaces As Integer, intField As Integer) As String

    Select Case intSpaces
        Case Is = 1 ' forename & surname
            If intField = 1 Then
                AssignName = getpart(Text, delim, 0)
            Else
                AssignName = getpart(Text, delim, 1)
            End If
        Case Is = 2 ' forename, middle name, and surname
            If intField = 1 Then
                AssignName = getpart(Text, delim, 0)
            ElseIf intField = 2 Then
                AssignName = getpart(Text, delim, 1)
            Else
                AssignName = getpart(Text, delim, 2)
            End If
        Case Is = 3 ' forename, 2 middle names, and surname
            If intField = 1 Then
                AssignName = getpart(Text, delim, 0)
            ElseIf intField = 2 Then
                AssignName = getpart(Text, delim, 1)
            ElseIf intField = 3 Then
                AssignName = getpart(Text, delim, 2)
            Else
                AssignName = getpart(Text, delim, 3)
            End If
    End Select

End Function

The code above is half pseudo-

 

[Eddie Mason]

Hi Mile-O-Phile,

I’ve now got a module that has three functions:
CountSpaces
AssignName
GetPart

I have placed in my query the following “Field1: AssignName(CountSpaces([AssDet]),1)” and when I run the query I get the following error message: “A delimiter must be a single character.” and this keeps repeating everytime I press OK until the query has been run. Is this because some of the records may have only one word in the [AssDet] field?

Regards

Eddie

 

[Mile-O]

One question then...

Part1: John
Part2: Paul
Part3: Simon
Part4: Smith

If the name was John Smith, would you want the result like this:

Part1: John
Part2:
Part3:
Part4: Smith

or this:

Part1: John
Part2: Smith
Part3:
Part4:

 

[Eddie Mason]

Hi Mile-O-Phile,

What I actualy want to do is replace the name field is in a report with one that shows the name as initials. I beleived that the easiest way of achieving this is to split the name into its component Parts Name1;Name2;Name3;Name4, and then I can glue them together as a single Initials field: FldInitials:left([Name1],1) & Left([Name2],1) & Left([Name3],1)......ect.

Does this help?

Regards

Eddie

 

[Mile-O]

I've done two queries in here - one does the split, one makes the initials.

 

[Mile-O]

Actually, do you have A2000 or above?

 

[Mile-O]

Sometimes, I do things the long way.

If you are just after the initials then this function is all you need.

This is why I ask about A2000 and above...

Public Function GetInitials(strText As String) As String

    On Error GoTo Err_ErrorHandler
    
    Dim intCounter As Integer
    Dim strTemp() As String

    strTemp() = Split(strText)

    For intCounter = LBound(strTemp()) To UBound(strTemp())
        GetInitials = GetInitials & Left(strTemp(intCounter), 1)
    Next intCounter
    
Exit_ErrorHandler:
    Exit Function
    
Err_ErrorHandler:
    GetInitials = vbNullString
    Resume Exit_ErrorHandler

End Function

Simply call it in the query:

Initials: GetInitials([MyField])​

 

[o1110010]

Sometimes, I do things the long way.

Seems like the long way is always the first to come to my mind recently. Making coding tediously fun.

 

[Eddie Mason]

Hi Mile-O-Phile,

Many thanks for that, its working great.

Regards

Eddie