Compare data in different formats

[lulu801]

I am looking to make a name comparison. In one field, the name will show as:
Smith, John
ABC Vending
Smith John Revocable Trust
Deluca Mario Di


The field I am comparing to will show:
John and Mary Smith
ABC Vending Company
John Smith Revocable Tr dated 1/11/03
Mario & Diane Deluca

I have tried using the mid functions to compare the left 4 characters to the right 4 characters, and that obviously works in the first example. I added a comparison of the left 4 to the left 4 to cover the 2nd example. I can live with the less frequent issues of example 3, but example 4 is too common.

IF Left ({ROD_TAX_RESPONSIBLE_PARTY_DIM.NAME_LINE1},6 ) =
Left ({ROD_TAX_RESPONSIBLE_PARTY_DIM.NAME_SEARCH_LINE},6 )
THEN "Y"
ELSE "N"
-----
IF Right ({ROD_TAX_RESPONSIBLE_PARTY_DIM.NAME_LINE1},4 ) =
Left ({ROD_TAX_RESPONSIBLE_PARTY_DIM.NAME_SEARCH_LINE}, 4)
THEN "Y"
ELSE "N"
-----
if {@Test1} = "Y"
or {@Test2} = "Y"
THEN "Y"
ELSE "F"
-----

(The report user does not like TRUE AND FALSE - wants Yes or No.)

I'd appreciate any suggestions.

 

[lulu801]

FORGOT TO MENTION - CR VERSION 11

 

[Madawc]

I'd advise splitting the name using SPLIT, with UBOUND to tell you the number of parts. Then do XX in YYY, where XX is one of the SPLIT fragments and YYY is the second field.

You have commas confusing the issue. Use a formula field with

REPLACE({field1), ",", "")

Even this will remain tricky, since some cases will match some of the words but not all. I'd suggest doing 'maybe' cases as a separate category.

As for 'True' and 'False', these values are returned by boolians, tests without an IF. They are convenient and if the user does not want to see them, you can have a separate display with something like

if @yourtest then "Yes" else "No"

Note that a test on a boolian does not need to say @yourtest = 'True', it is assumed.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10