Access表含标点记录标记的高效实现方案咨询

问题背景 #

我有一个包含195个字段的Access表，需要检查每个字段的记录是否包含标点符号，为含标点的记录标记“X”用于员工培训（不删除标点）。当前通过拆分查询实现，但字段过多会触发Access“查询过于复杂”错误，现咨询更高效的全表标点检查方法。

当前使用的查询示例：

UPDATE [GroupData] SET [GroupData].[Puntuation Error] = "X"
WHERE ((([GroupData].LASTNAME) Like "*.*" Or ([GroupData].LASTNAME) Like "*,*" Or ([GroupData].LASTNAME) Like "*!*" Or ([GroupData].LASTNAME) Like "*'*" Or ([GroupData].LASTNAME) Like '*"*' Or ([GroupData].LASTNAME) Like "*;*" Or ([GroupData].LASTNAME) Like "*:*" Or ([GroupData].LASTNAME) Like "*-*" Or ([GroupData].LASTNAME) Like "*(*" Or ([GroupData].LASTNAME) Like "*)*")) OR ((([GroupData].[LASTNAME]) Like "*.*" Or ([GroupData].[LASTNAME]) Like "*,*" Or ([GroupData].[LASTNAME]) Like "*!*" Or ([GroupData].[LASTNAME]) Like "*'*" Or ([GroupData].[LASTNAME]) Like '*"*' Or ([GroupData].[LASTNAME]) Like "*;*" Or ([GroupData].[LASTNAME]) Like "*:*" Or ([GroupData].[LASTNAME]) Like "*-*" Or ([GroupData].[LASTNAME]) Like "*(*" Or ([GroupData].[LASTNAME]) Like "*)*")) OR  ((([GroupData].FIRSTNAME) Like "*.*" Or ([GroupData].FIRSTNAME) Like "*,*" Or ([GroupData].FIRSTNAME) Like "*!*" Or ([GroupData].FIRSTNAME) Like "*'*" Or ([GroupData].FIRSTNAME) Like '*"*' Or ([GroupData].FIRSTNAME) Like "*;*" Or ([GroupData].FIRSTNAME) Like "*:*" Or ([GroupData].FIRSTNAME) Like "*-*" Or ([GroupData].FIRSTNAME) Like "*(*" Or ([GroupData].FIRSTNAME) Like "*)*")) OR ((([GroupData].[MIDDLENAME]) Like "*.*" Or ([GroupData].[MIDDLENAME]) Like "*,*" Or ([GroupData].[MIDDLENAME]) Like "*!*" Or ([GroupData].[MIDDLENAME]) Like "*'*" Or ([GroupData].[MIDDLENAME]) Like '*"*' Or ([GroupData].[MIDDLENAME]) Like "*;*" Or ([GroupData].[MIDDLENAME]) Like "*:*" Or ([GroupData].[MIDDLENAME]) Like "*-*" Or ([GroupData].[MIDDLENAME]) Like "*(*" Or ([GroupData].[MIDDLENAME]) Like "*)*")) OR ((([GroupData].NPI) Like "*.*" Or ([GroupData].NPI) Like "*,*" Or ([GroupData].NPI) Like "*!*" Or ([GroupData].NPI) Like "*'*" Or ([GroupData].NPI) Like '*"*' Or ([GroupData].NPI) Like "*;*" Or ([GroupData].NPI) Like "*:*" Or ([GroupData].NPI) Like "*-*" Or ([GroupData].NPI) Like "*(*" Or ([GroupData].NPI) Like "*)*"));

解决方案 #

方法1：VBA遍历字段检查（推荐，适合大量字段） #

这是最灵活的方式，完全不受字段数量限制，执行效率也更高。

操作步骤：

1. 打开Access数据库，按Alt+F11打开VBA编辑器。
2. 插入新模块（右键左侧项目窗口->插入->模块），粘贴以下代码：

Sub MarkPunctuationErrors()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim fld As DAO.Field
    Dim punctuationChars As String
    Dim hasPunctuation As Boolean
    Dim i As Integer
    
    ' 定义要检查的标点符号，可根据需求增减
    punctuationChars = ".,!""';:-()"
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset("GroupData", dbOpenDynaset)
    
    ' 遍历每条记录
    Do While Not rs.EOF
        hasPunctuation = False
        
        ' 遍历当前记录的所有字段
        For Each fld In rs.Fields
            ' 跳过标记字段本身，避免误判
            If fld.Name <> "Puntuation Error" Then
                ' 字段不为空时才检查
                If Not IsNull(fld.Value) Then
                    ' 逐个检查标点是否存在
                    For i = 1 To Len(punctuationChars)
                        If InStr(fld.Value, Mid(punctuationChars, i, 1)) > 0 Then
                            hasPunctuation = True
                            Exit For ' 找到标点就停止当前字段检查
                        End If
                    Next i
                End If
            End If
            
            If hasPunctuation Then Exit For ' 找到标点就停止当前记录的其他字段检查
        Next fld
        
        ' 标记含标点的记录
        If hasPunctuation Then
            rs.Edit
            rs("Puntuation Error") = "X"
            rs.Update
        End If
        
        rs.MoveNext
    Loop
    
    ' 清理资源
    rs.Close
    db.Close
    Set rs = Nothing
    Set db = Nothing
    
    MsgBox "标点检查标记完成！"
End Sub

3. 点击工具栏的运行按钮（绿色三角），等待执行结束即可。

方法2：分批次执行更新查询（无需VBA） #

如果不想用代码，可以把195个字段分成若干组，每组20个左右，分别写更新查询，避免单个查询过于复杂。

示例（第一批字段的查询）：

UPDATE [GroupData] SET [GroupData].[Puntuation Error] = "X"
WHERE [GroupData].[Puntuation Error] Is Null 
AND (
    [LASTNAME] Like "*[.,!""';:-()]*" 
    OR [FIRSTNAME] Like "*[.,!""';:-()]*"
    OR [MIDDLENAME] Like "*[.,!""';:-()]*"
    -- 继续添加其他字段，控制在20个以内
);

注意加上[Puntuation Error] Is Null条件，避免重复标记已经处理过的记录。写完一批执行一批，直到所有字段都覆盖。

方法3：VBA+正则表达式（精准匹配进阶） #

如果需要更灵活的标点匹配规则，比如匹配所有非字母数字的字符，可以用正则表达式：

1. 在VBA编辑器中，点击顶部菜单栏“工具->引用”，勾选“Microsoft VBScript Regular Expressions 5.5”。
2. 插入新模块，粘贴以下代码：

Sub MarkPunctuationErrorsWithRegex()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim fld As DAO.Field
    Dim regex As New RegExp
    Dim hasPunctuation As Boolean
    
    ' 正则表达式规则：匹配指定标点，可修改
    regex.Pattern = "[.,!""';:-()]"
    regex.Global = True ' 全局匹配
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset("GroupData", dbOpenDynaset)
    
    Do While Not rs.EOF
        hasPunctuation = False
        
        For Each fld In rs.Fields
            If fld.Name <> "Puntuation Error" Then
                If Not IsNull(fld.Value) Then
                    ' 用正则检查是否含标点
                    If regex.Test(fld.Value) Then
                        hasPunctuation = True
                        Exit For
                    End If
                End If
            End If
            
            If hasPunctuation Then Exit For
        Next fld
        
        If hasPunctuation Then
            rs.Edit
            rs("Puntuation Error") = "X"
            rs.Update
        End If
        
        rs.MoveNext
    Loop
    
    rs.Close
    db.Close
    Set rs = Nothing
    Set db = Nothing
    Set regex = Nothing
    
    MsgBox "标点检查标记完成！"
End Sub

如果要匹配所有非字母数字字符，把regex.Pattern改成"\W"（注意下划线_会被视为字母数字，若要排除下划线，用[^\w_]）。

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