Access 2007数据库姓名字段解析求助：含&符号的夫妻姓名拆分处理

Got it, let's work through this name parsing challenge step by step. The key is to leverage Access's string functions to handle both single and couple entries based on your Counter field, and cleanly remove that tricky & for spouse entries.

Option 1: Direct Query Expressions (No VBA Needed) #

You can build calculated fields directly in a select query to extract the required values without writing any code. Here's how:

1. Extract Last Name (Works for Both Single & Couple Entries) #

Since both formats have the last name before the first comma, we can use the same expression for all records:

LastName: Left([Fullname], InStr([Fullname], ", ") - 1)

• InStr([Fullname], ", ") finds the position of the comma + space in the name string.
• Left(...) grabs everything before that position to get the last name.

2. Extract Primary Name (Single Person or First Spouse) #

Use an IIf statement to switch logic based on Counter:

PrimaryName: IIf(
    [Counter] = 1,
    Mid([Fullname], InStr([Fullname], ", ") + 2),  -- For singles: grab everything after the comma+space
    Mid(
        [Fullname],
        InStr([Fullname], ", ") + 2,
        InStr([Fullname], " & ") - InStr([Fullname], ", ") - 2
    )  -- For couples: grab text between comma+space and &+space
)

3. Extract Spouse Name (Only for Couple Entries) #

Again, use IIf to return a value only when Counter=2:

SpouseName: IIf(
    [Counter] = 2,
    Mid([Fullname], InStr([Fullname], " & ") + 3),  -- Grab everything after &+space
    Null
)

Option 2: VBA Custom Function (More Flexible for Edge Cases) #

If you need to handle minor format inconsistencies (like extra spaces around &), a VBA function gives you more control. Here's how to set it up:

1. Open your Access database, press Alt+F11 to open the VBA editor.
2. Insert a new module (Insert > Module).
3. Paste this code:

Function ParseName(strFullName As String, intCounter As Integer) As Variant
    ' Returns an array: (0=LastName, 1=PrimaryName, 2=SpouseName)
    Dim arrResult(2) As String
    Dim intCommaPos As Integer
    Dim intAmpPos As Integer
    
    ' Find the position of the comma+space
    intCommaPos = InStr(strFullName, ", ")
    
    If intCommaPos > 0 Then
        ' Extract last name
        arrResult(0) = Left(strFullName, intCommaPos - 1)
        
        If intCounter = 1 Then
            ' Single entry: get everything after comma+space
            arrResult(1) = Trim(Mid(strFullName, intCommaPos + 2))
            arrResult(2) = ""
        ElseIf intCounter = 2 Then
            ' Couple entry: find the &+space
            intAmpPos = InStr(strFullName, " & ")
            If intAmpPos > 0 Then
                ' Extract primary name between comma+space and &+space
                arrResult(1) = Trim(Mid(strFullName, intCommaPos + 2, intAmpPos - intCommaPos - 2))
                ' Extract spouse name after &+space
                arrResult(2) = Trim(Mid(strFullName, intAmpPos + 3))
            Else
                ' Fallback if & is missing (handle bad format)
                arrResult(1) = Trim(Mid(strFullName, intCommaPos + 2))
                arrResult(2) = ""
            End If
        End If
    Else
        ' Fallback for entries without a comma (invalid format)
        arrResult(0) = strFullName
        arrResult(1) = ""
        arrResult(2) = ""
    End If
    
    ParseName = arrResult
End Function

4. Save the module (name it something like NameParsing).

Now you can use this function in your query:

LastName: ParseName([Fullname], [Counter])(0)
PrimaryName: ParseName([Fullname], [Counter])(1)
SpouseName: ParseName([Fullname], [Counter])(2)

This function adds Trim() to clean up any extra spaces, and includes fallbacks for unexpected formats—handy if your data has minor inconsistencies.

Testing Tips #

• Run a test query with a few sample records to verify:
  • Single entry: Henderson, Sarah → LastName=Henderson, PrimaryName=Sarah, SpouseName=Null
  • Couple entry: Smithman, Harry & Diana → LastName=Smithman, PrimaryName=Harry, SpouseName=Diana

内容的提问来源于stack exchange，提问作者Alan