Our department uses a Microsoft Access database to manage roughly 300,000 records. The front-end of the system allows users to add comments to a donor’s record. As you might guess, the comment field became the wild west with no standards for how comments were input. To make matters worse, prior to me starting here, in an instance where a donor already had a comment assigned to their record, rather than creating a new comment (a new line in the back-end table), the staff would open the existing comment and add additional notes to the end. ONLY DELIMITED BY A SPACE!

One of the instances where a comment would be added is if a duplicate record was found. The duplicate would be disabled with a note listing the original records ID for reference.

The time has come for us to migrate to a new system and I would love to pull these ID numbers out of the comment field so they can be added to a new table and used to link the original and duplicate records together before we import unnecessary data into a new database.

The problem I’m running into is; how do I write a query to extract the referenced ID value from the middle of a comments field that is a text field when there was no ? All IDs are 7 digits long. Below are a couple examples of how the roughly 4,000 instances of these comments were input.

• dup see 1189827, Rita is Mrs. White
• 4-16-18 d DUPE – 1344126
• d DUPE 2021108. AB.
• Duplicate 1717677 Missie RIP
• 4-10-14 use DUP #1323678 as that is where the history is located

In searching forums, I was able to build a public function that I could reference in a query but it pulls in all numeric values in the string, not just those that are in a string of 7 consecutive digits?

Public Function ExtractDonorID(strInput) As String
    Dim strResult As String, strCh As String
    Dim z As Integer
    If Not IsNull(strInput) Then
        For z = 1 To Len(strInput)
            strCh = Mid(strInput, z, 1)
            Select Case strCh
                Case "0" To "9"
                    strResult = strResult & strCh
                Case Else
            End Select
        Next z
    End If
    ExtractDonorID= strResult
End Function

Any Ideas?