在VBA Access中将全名字符串拆分为First，Last和Middle初始 [英] Splitting a full name string into First, Last and Middle Initial in VBA Access

问题描述

首先，从策略上来说，我是完全不熟悉VBA for Access编码的人，因此可能会有很多错误。但无论如何，我的名字和中间的缩写都有些问题。目前，这些名称在[lastName，firstName middleInitial]中格式化为字符串。我有3个文本框，分别标记为lastName firstName和middleInitial。下面的代码应该执行的操作是将该字符串分成三个适当的框，但是将其挂在名字上，在那里它不知道如何在空格处停下来，或者切断了几个字符，中间的首字母是仅当他们有中间的首字母时才起作用，但如果找不到中间的首字母就抛出错误。我一直在努力试图弄清楚头发，因此我陷入了僵局，在这里问。

First off apoligies, I am completely new to coding in VBA for Access, so there are probably a lot of errors. but anyways, I'm having a bit of issues with the first name and that middle initial. Currently the names are formatted as a string in [lastName, firstName middleInitial] I have 3 text boxes that are labled lastName firstName and middleInitial. What the below code should do is separate that string into the three approrpriate boxes but it gets hung up on the first name, where it doesn't know how to stop at the space or it cuts off a few characters, and the middle initial where it only works if they have a middle initial, but is throwing an error if it doesnt find one. I've been tearing my hair out trying to figure it out, and thus I have come to an impass and am asking here.

非常感谢！

Private Sub cbxMbr_AfterUpdate()

    If Not Me.opgMngRoster.Value = 1 Then
        'Member's Last Name
        Dim lastName As String
        lastName = Left(cbxMbr.Text, (InStr(1, cbxMbr.Text, ",") - 1))
        Me.txtLastName.Value = lastName

        'Member's First Name
        Dim firstName As String
        firstName = Mid(cbxMbr.Text, InStr(1, cbxMbr.Text, " "), (Len(cbxMbr.Text) - InStr(1, cbxMbr.Text, " ")))
        Me.txtFirstName.Value = firstName

        'Member's Middle Initial
        Dim midName As String
        midName = Mid(cbxMbr.Text, InStr(InStr(1, cbxMbr.Text, " ") + 1, cbxMbr.Text, " "))

        If midName = vbNullString Then
            Me.txtMidInit.Value = " "
        Else
            Me.txtMidInit.Value = midName
        'DoCmd.RunSQL "SELECT MEMBER ROSTER WHERE "
        End If
    End If

End Sub

推荐答案

您可以使用 Split ：

' "Duck, Donald D."
' "Duck, Donald"

Me!txtLastName.Value = Split(Me!cbxMbr.Value, ",")(0)
' Duck

Me!txtFirstName.Value = Split(Trim(Split(Me!cbxMbr.Value, ",")(1)), " ")(0)
' Donald

Me.txtMidtInit.Value = Split(Trim(Split(Me!cbxMbr.Value, ",")(1)) & " ", " ")(1)
 ' D. or zero-length string.

这篇关于在VBA Access中将全名字符串拆分为First，Last和Middle初始的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！