Re: [MSAccessProfessionals] Ignoring non-alphanumeric characters when searching for matches

Just make up and use a little function that would clean all the unneeded characters, so, for example:

Public Function CleanChars(inStr1 As String)

Dim iX As Integer

Dim testChar

    For iX = 1 To Len(inStr1)

    testChar = Mid(inStr1, iX, 1)

    If IsNumeric(testChar) Or IsAlphabetic(testChar) Then

        CleanChars = CleanChars & testChar

    End If

Next iX

End Function

On Tuesday, September 13, 2022 at 12:49:05 PM EDT, penny.price via groups.io <penny.price=denvergov.org@groups.io> wrote:

Hi All, it's Penny in Denver having a little struggle with yet another Access database I've created. I'm tracking Abandoned Vehicles for the city these days. I often get duplicate complaints on the same vehicle. I don't wish to get rid of these duplicates as they are from different parties and often contain slightly different information, however I do need to track them which I do by comparing license plate numbers, using the following formula: DCount("*","Contacts -T,"[VEH Pic No]="& Chr(34) & [VEH Pic No] $ Chr(34))>1

 

This causes the current entry to show up in red  if there's a match in a prior complaint and I then search that field for the others. This works well unless some of the plate numbers have been entered as ABC1234 and others as ABC-1234. Is there any way to modify the formula to ignore any part of an entry that is not alphanumeric so it could find them all???

Hopeful Penny in the Mile High City!