Comparing Similar but not exact data

[Guest]

I have two Name fields I want to compare, the fields are identical except for
a comma in one of the tables separating the first name and last name. In the
second table they are separated by a space. How do I get Access to recognize
and match either partially similar or similar except for the comma and return
the values.
When I run a compare the queries return nothing.

 

[Guest]

You can use the InStr function to find the location of the comma, and use
that number and the Len function to build Left() and Right() functions to
isolate the two halves and compare them, or to pull out the space and comma
and them compare.

Might be easier in VB than trying to do as a set of function calls, but same
general idea fits both methods.

- Phil Freihofner

 

[Jeff Boyce]

Brad

Define "partially similar".

Which of the following are "partially similar":

Bob Jones
Robert Jones
"J.J." Jones
Roberta Jones
Bobby Jones
Bobbi Jones
Robert Smith

?!

Good luck

Jeff Boyce
<Access MVP>

 

[Marshall Barton]

I have two Name fields I want to compare, the fields are identical except for
a comma in one of the tables separating the first name and last name. In the
second table they are separated by a space. How do I get Access to recognize
and match either partially similar or similar except for the comma and return
the values.
When I run a compare the queries return nothing.

How about something a little hokey:

for the criteria of the field with a space use:
=Replace(Replace(fieldwithcomma, ",", " "), " ", " ")
or something like that?