Avatar of newholyman
newholymanFlag for United States of America asked on

Fullname split

Please share the vba code to split this full name.

MICHELLE J lastname & JOE E lastname

Out put
last name= lastname

firstname= MICHELLE
middlename= j

first name= joe
middlename=E
lastname=lastname

There will be other data in the fullname field:
firstname lastname
firstname middlename las name
 and therw can suffix
firstname middlename lastname suffix

Each name is seperate by a space.


Thank you for your help and support

Thank you
Microsoft AccessMicrosoft ApplicationsMicrosoft Office

Avatar of undefined
Last Comment
Jeffrey Coachman

8/22/2022 - Mon
Jeffrey Coachman

Will all six string segments always be present?
SOLUTION
GrahamSkan

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Jeffrey Coachman

Results:results
GrahamSkan

The previous attempt ignored the suffix.

This one attempts to deal with it but if an entry comprises firstname, lastname  & suffix, it will be treated as firstname, middlename & lastname. The only way round that is to have some way of distinguishing between a last name and suffix.
Sub splitNamestring2()
    Dim i As Integer
    Dim n As Integer
    Dim Names() As String
    Dim TempNames() As String
    Dim FullNames() As String
    Dim strNameString As String
    
    strNameString = "MICHELLE J lastname & JOE E lastname"
    FullNames = Split(strNameString, " & ")
    ReDim Names(3, UBound(FullNames))
    
    For i = 0 To UBound(FullNames)
        TempNames = Split(FullNames(i), " ")
        Names(0, i) = TempNames(0) 'first name
        n = UBound(TempNames)
        'first name
        Select Case n
            'last name
            Case 1
                Names(2, i) = TempNames(1) 'last name
            Case 2
                Names(1, i) = TempNames(1) 'middle name
                Names(2, i) = TempNames(2) 'last name
            Case 3
                Names(1, i) = TempNames(1) 'middle name
                Names(2, i) = TempNames(2) 'last name
                Names(3, i) = TempNames(3) 'suffix
        End Select
    Next i
End Sub

Open in new window

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER
newholyman

There is a space between the lastname and the suffix.

Thak you.
ASKER
newholyman

qeury16 gave me this error.
ASKER
newholyman

Error in attached file
test-for-fixed.docx
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Jeffrey Coachman

When you open the database, press Alt+F11 to go immediately into the into the VBA Editor.
Then click: Debug-->Compile
Then run the compact/Repair utility
Then open the query
ASKER
newholyman

I aplogize I did not import the module1. That correct the error.
i is not splitting this name correct
DAVID L & KATHRYN J lastname

DAVID,L,&,KATHRYN,J,lastname

fn1=DAVID
mn1=L
ln1=&
fn2=J
mn2=lastname
ln2=blank or space
yourname2=DAVID,L,&,KATHRYN,J,lastname

Thank you
GrahamSkan

Are any of your comments relevant to my contribution? If not, I'll stop now.
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER
newholyman

I'm using Boag2000 query examples. Thank you for your help.
Jeffrey Coachman

<i is not splitting this name correct
DAVID L & KATHRYN J lastname>
...That is not the original format you specified...

What you specified was:
MICHELLE J lastname & JOE E lastname
(all six names are present)
...besides, you are not telling us what you want returned in each field, for each different "name" format/combination.

This is why I asked in:
https://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_28236751.html#a39484184
   "Will all six string segments always be present? "
...to which you never replied...?

There is no one function that will catch every possible name combination. (missing middle names(s), ...etc, one missing last name, two middle names, ...etc)
 So all I can offer is what I posted...

JeffCoachman
Jeffrey Coachman

GrahamSkan may be willing to customize his solution for this new contingency.

Again, all I can offer is what I posted as per your original requirement...

...but as I stated, if there are more than two possible Name "combinations", then this may become more complicated than it is worth.

JeffCoachman
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
newholyman

Sir, when i posyed the question I list the following.

There will be other data in the fullname field:
firstname lastname
firstname middlename last name
 and therw can suffix
firstname middlename lastname suffix

Each name is seperate by a space.
ASKER
newholyman

When there two firsr name ther always be &.  That is way I can tell that ther two first names
GrahamSkan

OK. This is a collaborative forum. What we post here can be searched for and seen by others. Contributions hidden in links will not appear in any search.

It doesn't apply here, but off-site links have a tendency to evaporate and become useless in the future.

Also, this is intended to be a 'how-to', as opposed to a 'do-it-for-you' forum, so it is better to see what you, as well as later searchers, can learn without following what could turn out to be a series of links.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Jeffrey Coachman

GrahamSkan,

Was you post above meant for this thread?

Jeff
ASKER
newholyman

Sorry sir I posted the points on wrong name. How do I change my error
Jeffrey Coachman

click the request Attention link and explain what it is that you want to do...
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
GrahamSkan

Jeez,  I am confident that I gave good advice, but I thought that you were ignoring me
Jeffrey Coachman

I'm a bit confused too...