Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Access Forms _ Parsing Name & Surname

Posted by: Consonanza May 22 2020, 10:32 AM

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.



Posted by: john_willmott May 22 2020, 10:45 AM

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

Posted by: Consonanza May 22 2020, 11:35 AM

That works for me. Thanks for you quick reply.

Posted by: cheekybuddha May 22 2020, 11:45 AM

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]

Posted by: Consonanza May 23 2020, 02:07 AM

Thanks for that alternative, tidy solution. Something there for me to learn.

Posted by: missinglinq May 23 2020, 07:41 AM

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)>

Posted by: FrankRuperto May 23 2020, 07:55 AM

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


 

Posted by: Jeff B. May 23 2020, 07:59 AM

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).

Posted by: FrankRuperto May 23 2020, 09:38 AM

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