Name Splitter Problem (1 Viewer)

Rich_Lovina

Registered User.
Local time
Today, 20:36
Joined
Feb 27, 2002
Messages
225
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

Registered Yoozer
Local time
Today, 04:36
Joined
Jan 14, 2004
Messages
1,036
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

Registered User.
Local time
Today, 20:36
Joined
Feb 27, 2002
Messages
225
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

"The Shoe"
Local time
Today, 05:36
Joined
Jan 20, 2004
Messages
707
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

Back once again...
Local time
Today, 09:36
Joined
Dec 10, 2002
Messages
11,316
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

Registered Yoozer
Local time
Today, 04:36
Joined
Jan 14, 2004
Messages
1,036
So then this would be an example of your functions using Split, and checking the first name for 2 chars:

Code:
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

Registered Perpetrator
Local time
Today, 03:36
Joined
Dec 5, 2000
Messages
263
Long time, indeed!

And if you're not using A2k or better:

Code:
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

Registered User.
Local time
Today, 20:36
Joined
Feb 27, 2002
Messages
225
RichO said:
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

Registered User.
Local time
Today, 20:36
Joined
Feb 27, 2002
Messages
225
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

Registered Yoozer
Local time
Today, 04:36
Joined
Jan 14, 2004
Messages
1,036
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

Registered User.
Local time
Today, 20:36
Joined
Feb 27, 2002
Messages
225
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

Registered User.
Local time
Today, 20:36
Joined
Feb 27, 2002
Messages
225
Error....Variable Not Defined

Correction...variable not defined
 

Rich_Lovina

Registered User.
Local time
Today, 20:36
Joined
Feb 27, 2002
Messages
225
Dougs Code works perfectly in A2K

Code:
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

Registered Yoozer
Local time
Today, 04:36
Joined
Jan 14, 2004
Messages
1,036
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).
 
Last edited:

Users who are viewing this thread

Top Bottom