Comparing similar text fields?

[Brian Cleary]

I've got two tables that contain duplicates that I am
trying to remove but they may be slightly different, a
missing comma or no period after "Inc", is there a way to
compare the values using a LIKE type command but for the
value of the second table field? Thanks in advance.

Brian C.

 

[John Vinson]

I've got two tables that contain duplicates that I am
trying to remove but they may be slightly different, a
missing comma or no period after "Inc", is there a way to
compare the values using a LIKE type command but for the
value of the second table field? Thanks in advance.

Brian C.

Sure; a WHERE clause such as

WHERE Table1.CompanyName LIKE Table2.CompanyName & "*"

 

[John Spencer (MVP)]

It kind of depends.

John P. Spencer, Jr. compared to John P Spencer Jr

That wouldn't match with a simple Like. You would need to use a vba function to
strip the two down to John P Spencer Jr and then compare those two values.

You could use nested Replace statements if you are using Access 2002 or later.
For example, to remove periods and commas and compare the values.

WHERE Replace(Replace(Table1.FieldA,","),".") = Replace(Replace(Table2.FieldB,","),".")

If you needed something more complex than that, you probably would be better off
writing a function to strip out all "extra" characters. Also, you would need to
look at Inc vice Incorporated and Company vice Co and ...