Name Splitter Problem

[Rich_Lovina]

My code splits a single field (NameAGS = “FirstName Middlename Lastname” into 3 separate fields “Inits” “1stname” and “Surname” inserted in an Append query.

Problem is we now have some mixed records in NameAGS such as AB Smith, in with Ann Smith or Ann Marie Smith, and the splitter is returning Inits=”A”, 1stName=”AB” and Surname=”Smith”.

What conditional code do I insert in the module to ensure that 2-char names return Inits=”AB” and that 1stName is blank?

Code is:
Function PrsIntls(NameAgs As String) As String
Dim SpPntr As Integer
Dim Intls As String

Intls = left(NameAgs, 1)

SpPntr = InStr(1, NameAgs, " ")

While SpPntr <> 0
Intls = Intls & Mid(NameAgs, SpPntr + 1, 1)
SpPntr = InStr(SpPntr + 1, NameAgs, " ")
Wend

PrsIntls = left(Intls, Len(Intls) - 1)

End Function

Function PrsLstNm(NameAgs As String) As String
Dim SpPntr As Integer
Dim LstNmPntr As Integer

SpPntr = InStr(1, NameAgs, " ")
LstNmPntr = 1

While SpPntr <> 0
LstNmPntr = SpPntr + 1
SpPntr = InStr(SpPntr + 1, NameAgs, " ")
Wend

PrsLstNm = Mid(NameAgs, LstNmPntr)
End Function

Function PrsFrstNm(NameAgs As String) As String
Dim SpPntr As Integer

SpPntr = InStr(1, NameAgs, " ")

'Intls = ""

If SpPntr <> 0 Then
'if there is a space then there is a first name or initial for a first name
PrsFrstNm = left(NameAgs, SpPntr - 1)
If Len(PrsFrstNm) = 1 Then
PrsFrstNm = ""
End If
End If

Thanks in advance, as have been away from this coding for some time.

 

[RichO]

Problem is we now have some mixed records in NameAGS such as AB Smith, in with Ann Smith or Ann Marie Smith, and the splitter is returning Inits=”A”, 1stName=”AB” and Surname=”Smith”.

I'm not sure that I follow what you mean. Is the code returning A, AB & Smith for all 3 names (AB Smith, Ann Smith, and Ann Marie Smith)?

What conditional code do I insert in the module to ensure that 2-char names return Inits=”AB” and that 1stName is blank?

When you come across a 2 char name are you saying you want the code to assume it's first & middle initials and return a blank result for the first name, ie: AB Smith would result in Inits = "ABS", 1stName = "", Surname = "Smith"

What about legitimate 2 char first names like Al & Bo?

The coding is pretty straightforward, just want to make sure I understand your question before proceeding.

 

[Rich_Lovina]

Tks your input. Yes it is easier to manually correct legitimate 2 char names (or later make an exception condition).

In above example Ann Marie Smith correctly returns "AM Smith" but for rawdata AB Smith code returns Inits ="A" 1stname="AB" and Surname="Smith". In other words code is incorrectly populating those records with 1stnames field with AB CD or DF initials sets. Can forget about 3-set initials or 3 char names as these are less than 5 in 2,500 records, hence manually examined.

Thanks in advance

 

[Kodo]

I recommend looking into the SPLIT function.

If you're submitting each part of the name from a field.. then I would concatenate each part of the field with a comma separator and then split on the comma. You can then use the index of the array to assign each value for inserting to the database.

lets say I get this from a form:

FirstName=me.firstname
Lastname=me.lastname
init=me.init

wholename=Firstname &","& init &","& Lastname

then yo do

mynamearray=split(wholename,",")

each part of the array is now indexed from 0.
so mynamearray(0) would be FirstName
mynamearray(1) would be the initial
mynamearray(2) would be the lastname

 

[Mile-O]

Split function definitely.

I also posted a bit of code on the Code Repository forum that allows you take whichever part of a delimited (in this case by spaces) string you want.

 

[RichO]

So then this would be an example of your functions using Split, and checking the first name for 2 chars:

Function PrsIntls(NameAgs As String) As String
    Dim NameArray As Variant
    Dim i         As Integer
    NameArray = Split(Me.NameAgs, " ")
    For i = 0 To UBound(NameArray)
       PrsIntls = PrsInitls & Left(NameArray(i), 1)
    Next i
    [COLOR=DarkOliveGreen]'Check if first name is 2 chars[/COLOR]
    If Len(NameArray(0)) = 2 Then
       PrsIntls = NameArray(0) & Left(NameArray(UBound(NameArray)),1)
    End If
End Function

Function PrsLstNm(NameAgs As String) As String
    Dim NameArray As Variant
    NameArray = Split(Me.NameAgs, " ")
    PrsLstNm = NameArray(UBound(NameArray))
End Function

Function PrsFrstNm(NameAgs As String) As String
    Dim NameArray As Variant
    NameArray = Split(Me.NameAgs, " ")
    PrsFrstNm = NameArray(0)
    [COLOR=DarkOliveGreen]If first name is 2 chars, then no first name[/COLOR]
    If Len(NameArray(0)) = 2 Then PrsFrstNm = ""
End Function

 

[DALeffler]

Long time, indeed!

And if you're not using A2k or better:

Function PrsIntls(NameAgs As String) As String
Dim SpPntr As Integer
Dim Intls As String

[COLOR=DarkOrange]NameAgs = LTrim(NameAgs)

SpPntr = InStr(1, NameAgs, " ")

If SpPntr = 3 Then
     PrsIntls = Left(NameAgs, 2)
     Exit Function
End If

Intls = Left(NameAgs, 1)[/COLOR]

While SpPntr <> 0
     Intls = Intls & [COLOR=DarkOrange]LTrim([/COLOR]Mid(NameAgs, SpPntr + 1, 1)[COLOR=DarkOrange])[/COLOR]
     SpPntr = InStr(SpPntr + 1, NameAgs, " ")
Wend

PrsIntls = Left(Intls, Len(Intls) - 1)

End Function

Function PrsLstNm(NameAgs As String) As String
Dim SpPntr As Integer
Dim LstNmPntr As Integer

SpPntr = InStr(1, NameAgs, " ")
LstNmPntr = 1

While SpPntr <> 0
LstNmPntr = SpPntr + 1
SpPntr = InStr(SpPntr + 1, NameAgs, " ")
Wend

PrsLstNm = Mid(NameAgs, LstNmPntr)
End Function

Function PrsFrstNm(NameAgs As String) As String
Dim SpPntr As Integer

[COLOR=DarkOrange]NameAgs = LTrim(NameAgs)[/COLOR]

SpPntr = InStr(1, NameAgs, " ")

If SpPntr <> 0 Then
    'if there is a space then there is a first name or initial for a first name
    PrsFrstNm = Left(NameAgs, SpPntr - 1)
        [COLOR=DarkOrange]If Len(PrsFrstNm) < 3 Then
            PrsFrstNm = ""
        End If[/COLOR]
End If
End Function

I have not thoroughly tested that code; be cautious (make back-ups!!!) (big grin!)).

 

[Rich_Lovina]

So then this would be an example of your functions using Split, and checking the first name for 2 chars:
Thanks greatly for your input. I'll give this one a try.

 

[Rich_Lovina]

Invalid Use of Me keyword

An error message is appearing in this version highlighting me in line 3 below.
Any ideas why??
Function PrsLstNm(NameAgs As String) As String
Dim NameArray As Variant
NameArray = Split(Me.NameAgs, " ")
PrsLstNm = NameArray(UBound(NameArray))
End Function

Thanks in advance

 

[RichO]

Oops... when I was testing the code I was using it with form controls and I missed that one when I was altering the code for the functions.

Simply remove the Me. so you have this...

NameArray = Split(NameAgs, " ")

 

[Rich_Lovina]

Still a dummy at this code, I replaced above line with
NameArray = Split(NameAgs, " ") in all 3 places, and now code is getting error

PrsInitls is not a valid variable

Any idea where I make it valid?
Thanks

 

[Rich_Lovina]

Error....Variable Not Defined

Correction...variable not defined

 

[Rich_Lovina]

Dougs Code works perfectly in A2K

Function PrsIntls(NameAgs As String) As String
Dim SpPntr As Integer
Dim Intls As String
Well, I must thanks Doug, your code does the job perfectly!

 

[RichO]

PrsInitls is not a valid variable

PrsIntls is the name of the function, there was another typo in my code (there is an extra "i" in there).