Mark Records with Punctuation Marks in Access Table-CodePudding

I have a table with 195 columns/fields. I need to check each field to see if any records contain punctuation marks. I would like to mark records containing punctuation marks with an “X” to take back to staff for training purposes – I do not want to remove the punctuation marks.

Currently, I have set up queries to add the 195 fields with criteria set to look for punctuation marks, but I have to split the process into several queries b/c I receive an Access error that the query is too complex if I add too many fields at one time.

Is there a more efficient way to check the entire table for punctuation marks?

As an example, below is one of my current queries

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 "*)*"));

CodePudding user response：

Firstly, a couple of suggestions. Please consider normalising your data - 195 fields in a table is normally an indication that the data is not normalised. Secondly, you should consider removing spaces in table/field names.

Having said that, I think that the way to go is to use a recordset that is built up of concatenated fields, and then use an array of punctuation marks to loop over these fields. Something like the below seems to work, although I have not tested this on a large number of fields:

Sub sCheckPunctuation()
    On Error GoTo E_Handle
    Dim db As DAO.Database
    Dim rsData As DAO.Recordset
    Dim strSQL  As String
    Dim astrPunctuation(1 To 4) As String
    Dim intLoop1 As Integer
    Set db = CurrentDb
    strSQL = "SELECT Punctuation_Error, " _
        & " BRD & BRD_EXP_DT2 & TIN AS Check1, " _
        & " BRD & BRD_EXP_DT2 & TIN AS Check2  " _
        & " FROM tblRoster;"
    Set rsData = db.OpenRecordset(strSQL)
    astrPunctuation(1) = "!": astrPunctuation(2) = "£": astrPunctuation(3) = "$": astrPunctuation(4) = "%"
    If Not (rsData.BOF And rsData.EOF) Then
        Do
            If InStr(rsData!Check1 & rsData!Check2, Chr(34)) > 0 Then
                With rsData
                    .Edit
                    !Punctuation_Error = "X"
                    .Update
                End With
            Else
                For intLoop1 = 1 To 4
                    If InStr(rsData!Check1 & rsData!Check2, astrPunctuation(intLoop1)) > 0 Then
                        With rsData
                            .Edit
                            !Punctuation_Error = "X"
                            .Update
                        End With
                        Exit For
                    End If
                Next intLoop1
            End If
            rsData.MoveNext
        Loop Until rsData.EOF
    End If
sExit:
    On Error Resume Next
    rsData.Close
    Set rsData = Nothing
    Set db = Nothing
    Exit Sub
E_Handle:
    MsgBox Err.Description & vbCrLf & vcbcrlf & "sCheckPunctuation", vbOKOnly   vbCritical, "Error: " & Err.Number
    Resume sExit
End Sub