UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Parsing Name & Surname, Any Version    
 
   
Consonanza
post May 22 2020, 10:32 AM
Post#1



Posts: 179
Joined: 1-June 10



On my form, I have a text box with name and surname (fldCombinedName) I have a small button to the side of the text box which when clicked parses the fldCombinedName to two separate text boxes (fldForename and fldSurname).

Here is the code that works fine;
CODE
Private Sub cmdParseCombinedName_Click()
Me!fldForename = Left([fldCombinedName], InStr(1, [fldCombinedName], " ") - 1)
Me!fldSurname = Right(Trim([fldCombinedName]), Len(Trim([fldCombinedName])) - InStr(1, [fldCombinedName], " "))

However, I want cater for the example (musical artists, therefore frequent) where fldCombinedName is a single word.
In those cases I want to have the code simply copy the fldCombined name to fldForename.

As it is I get "Error 5 Invalid procedure call or argument...." message.

How can I overcome this.


Go to the top of the page
 
john_willmott
post May 22 2020, 10:45 AM
Post#2



Posts: 536
Joined: 12-July 03
From: South Wales, UK


you could use the error:

Private Sub cmdParseCombinedName_Click()
on error goto errtrap
Me!fldForename = Left([fldCombinedName], InStr(1, [fldCombinedName], " ") - 1)
Me!fldSurname = Right(Trim([fldCombinedName]), Len(Trim([fldCombinedName])) - InStr(1, [fldCombinedName], " "))

endcode:
Exit sub

errtrap:
select case err
case 5
Me!fldForename = [fldCombinedName]
case else
msgbox err
end select
resume endcode
end sub
Go to the top of the page
 
Consonanza
post May 22 2020, 11:35 AM
Post#3



Posts: 179
Joined: 1-June 10



That works for me. Thanks for you quick reply.
Go to the top of the page
 
cheekybuddha
post May 22 2020, 11:45 AM
Post#4


UtterAccess Moderator
Posts: 13,045
Joined: 6-December 03
From: Telegraph Hill


Hello,

Another method would be to use the Split() function:
CODE
Private Sub cmdParseCombinedName_Click()

  Dim vSplit As Variant, strFName As String, strSName As String, i As Integer

  vSplit = Split(Me.fldCombinedName & vbNullString, " ")
  For i = 0 To UBound(vSplit)
    If i = 0 Then
      strFName = vSplit(i)
    Else
      strSName = strSName & " " & vSplit(i)
    End If
  Next i
  Me.fldForename = strFName
  Me.fldSurname = Mid(strSName, 2)

End Sub


[Edited, to add back spaces in last name]

--------------------


Regards,

David Marten
Go to the top of the page
 
Consonanza
post May 23 2020, 02:07 AM
Post#5



Posts: 179
Joined: 1-June 10



Thanks for that alternative, tidy solution. Something there for me to learn.
Go to the top of the page
 
missinglinq
post May 23 2020, 07:41 AM
Post#6



Posts: 4,704
Joined: 11-November 02



QUOTE
Something there for me to learn.


While you're in a learning mood/mode...the important thing to take away from this is to never put two or more pieces of data into a single Field...especially names!

The variety of name configurations is almost limitless...especially among performers!

Red Hot Chili Peppers
Alan Parsons Project
Junior Walker and the Allstars

and, for the seniors among us

Nat "King" Cole
J. Fred Muggs

Linq ;0)>

--------------------
BTW...The Monkey is laughing at me...not at any other poster!

The problem with making anything foolproof...is that fools are so darn ingenious!

All posts/responses based on Access 2003/2007
Go to the top of the page
 
FrankRuperto
post May 23 2020, 07:55 AM
Post#7



Posts: 1,108
Joined: 21-September 14
From: Tampa, Florida USA


QUOTE (Linq)
...never put two or more pieces of data into a single Field...especially names!

I disagree with "never". My pawn app's customer last name(s) field stores single or multiple last names in one field and users have never had any problems searching or sorting by this field. We have after update vba code for trimming extra spaces between names, only allowing English A to Z characters, and other formatting.

CODE
Public Function RemoveExtraSpaces(ByVal strText As String) As String
    Do Until InStr(1, strText, "  ") = 0
        strText = Replace(strText, "  ", " ", 1)
    Loop
    RemoveExtraSpaces = Trim(strText)
End Function

Private Sub txtLastName_AfterUpdate()

    If IsNull(txtLastName) Then Exit Sub
    
    txtLastName = Trim(UCase(txtLastName))
    txtLastName = RemoveExtraSpaces(txtLastName)
    txtLastName = Replace(txtLastName, " - ", "-")
    txtLastName = Replace(txtLastName, "- ", "-")
    txtLastName = Replace(txtLastName, " -", "-")
          
   txtDisplayCustomer.Requery
End Sub

This post has been edited by FrankRuperto: May 23 2020, 08:22 AM
Attached File(s)
Attached File  CustScreen.png ( 217.74K )Number of downloads: 8
 

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix, Oracle & PostgreSQL db's.
Go to the top of the page
 
Jeff B.
post May 23 2020, 07:59 AM
Post#8


UtterAccess VIP
Posts: 10,491
Joined: 30-April 10
From: Pacific NorthWet


I'll offer a couple more (counter-)examples to Linq's list:

* Cher
* Prince
* Otto von Bismark

You may end up splitting these as best you can get 'automatically', then apply USB-technology (Using Someone's Brain).

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
FrankRuperto
post May 23 2020, 09:38 AM
Post#9



Posts: 1,108
Joined: 21-September 14
From: Tampa, Florida USA


ADDENDUM: Forgot to include this sub in my post #7 for only allowing A to Z, and hyphen characters in name fields.

CODE
Private Sub txtLastName_KeyPress(KeyAscii As Integer)

    Select Case KeyAscii
        Case 65 To 90, 97 To 122 'alpha  A-Z, a-z
        Case 32     'space
        Case 45     '-
        Case vbKeyBack  'backspace in case of error
        Case Else
            KeyAscii = 0    'back to null
        Exit Sub
    End Select
    
End Sub

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix, Oracle & PostgreSQL db's.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    12th July 2020 - 03:15 PM