Access Code String problem

I am trying to manipulate a string. On the form with the control button "Greeting" a user enters his last name and then his first name. When you press the "Greeting" button I am trying to reverse the name to first name and last name in the output box below.

I can get the name to appear in the box with the welcome message but I can't get the name to reverse.
AccessDB.accdb
James CoatsComputer Info. Sys. StudentAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

IrogSintaCommented:
You can try something like this:
    Dim iPos As Integer
    Dim strName1 As String
    Dim strName2 As String

    iPos = InStr(Me.txtInput, " ")
    strName1 = Left(Me.txtInput, iPos - 1)
    strName2 = Mid(Me.txtInput, iPos)
    Me.lblOutPut.Caption = "Welcome " & StrConv(strName2 & " " & strName1, vbProperCase)
    

Open in new window

The problem though is that if that if you're having them enter their name with last name first, will they be adding a comma between the last name and first name?  If so, you'll need to account for that.
0
Fernando SotoRetiredCommented:
Hi SamuelClemmons;

Try this it should give you what you need.

Private Sub cmdGreeting_click()

Dim strLastName As String, strInput As String, strFirstName As String, int1 As Integer, intInput As Integer, int2 As Integer, int3 As Integer, strOutput As String, int4 As Integer
'Define textbox input
strInput = (txtInput.Value)
'Define array to hold name, first last
Dim names() As String
' split the strInput into seperate array values first element is lastname next element is firstname
names = Split(strInput)
' Format the output string
strOutput = "Welcome " & StrConv(names(1), vbProperCase) & " " & StrConv(names(0), vbProperCase)
' Display Welcome message
Me.lblOutPut.Caption = strOutput

End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
IrogSintaCommented:
Correction to my post on line 7:
 strName2 = Mid(Me.txtInput, iPos+1)

@Fernando, I was thinking of the Split function; however, I realized there would be problems if the last name had a space in it (i.e. De Vega, Von Trapp, etc).

Ron
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

James CoatsComputer Info. Sys. StudentAuthor Commented:
IrogSinta,

That works. How would you account for the comma? Could you comment on the lines 5, 6 and 7?

I understand that the "Left(me.txtInput, " ") will put the "strName1" to the left but don't understand the " iPos -1" part.
0
James CoatsComputer Info. Sys. StudentAuthor Commented:
Fernando,

I was asking a question of IrogSinta. I am looking at yours now. Thank you.
0
James CoatsComputer Info. Sys. StudentAuthor Commented:
Thank you both. I figured out how to handle the comma and have learned a lot from the two of you. Thanks again.
0
Fernando SotoRetiredCommented:
Not a problem Samuel, glad to have helped.
0
IrogSintaCommented:
Here's my code with comments.  iPos is used to store the character position of the space:
    Dim iPos As Integer
    Dim strName1 As String
    Dim strName2 As String
    Dim strFullname As String

    iPos = InStr(Me.txtInput, " ")                              'get character position of the 1st space in the input
    strName1 = Left(Me.txtInput, iPos - 1)                      'get all the characters to the left of space
    strName2 = Mid(Me.txtInput, iPos + 1)                       'get the characters starting from the position after the space
    strFullname = Replace(strName2 & " " & strName1, ",", "")   'remove any commas if any
    
    Me.lblOutPut.Caption = "Welcome " & StrConv(strFullname, vbProperCase)
    

Open in new window

The question must be asked though... how do you know the user will follow instructions and enter their last name first?  It is preferable to have separate textboxes for first name and last name.

Ron
0
IrogSintaCommented:
So did you see my comment about using the Split function?  It will not take care of spaces in Last Names yet you preferred this solution?
0
James CoatsComputer Info. Sys. StudentAuthor Commented:
IrogSinta,

Sorry I should have waited a little longer to allow you to finish with your solution. I would have preferred yours this is my fault.

Thank you for including the commenting on your code as well.

Sam
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.