John,
Thanks for your input.
I was not planning on linking the name field.
I have a clean set of records, and I would like to compare those with the
most recent set of records. If the fields in the most recent set of records
are different by having extra spaces, transposing of letters or having a
punctuation, I would not consider them different. Otherwise, those fields
are considered updated fields.
Basically, I want to filter the fields that are updated, and if those fields
are updated, I want to reflect the changes to my clean set of records.
I still suspect that having a pair of human eyes on the task will make it
easier.
You can write a small function (I'm sure it's been posted here...) to strip
out all nonalphabetic characters from a field: heck, it's late but it's not
hard:
Public Function JustAlpha(strIn As String) As String
Dim iPos As Integer
Dim chrNext As String
JustAlpha = ""
strIn = UCase(strIn)
For iPos = 1 to Len(strIn)
chrNext = Mid(strIn, iPos, 1)
If chrNext >= "A" AND chrNext <= "Z" Then
JustAlpha = JustAlpha & chrNext
End If
End Sub
This will take "Tommy Jones" and "Tommy Jones" and "T*O*M*M*Y _ J%o3N.eS" and
convert them to TOMMYJONES.
Switched characters... well, that's tougher since it's a combinatorial open
ended problem; and it will lead to false matches (Jane and Jean say). There
are some alphanumeric distance algorithms that could be used but they'd
probably be overkill here.