Intermediate formula help please 1

[ynott]

Here's the problem. Our database stores addresses as a verified address or a wrong address (really means unverified). The address is in three parts (Strt_num, Strt_name, and Loc_fetur <--- translates to digits street and the apt/suite).

Here's the problem, the attached formula (at bottom) works for about 90% of all addresses, but there is one problem. Our database is suppose to take out the entry in the wrong address table when the address is corrected (via software) and place in the verified address area. Approximately 10% of the time the address is mistakenly stored in both tables. Thus, I get the address duplicated in my crystal report . Any idea of how to change this formula to not have the wrong address populate if the verified address populates. TIA

PS - I have the Convert Null Value to default checked.

//Verified Addresses
stringvar StNo := {LOCATIONS.LOC_STRT_NUM};
stringvar Str := {LOCATIONS.STRT_NAME};
stringvar Apt := {LOCATIONS.LOC_FETUR};

//Unverified Addresses
stringvar Wrongaddr1 := {INCIDENT_LOC_EVENTS_VW.INCD_WRONG_STRT_NUM};
stringvar Wrongaddr2 := {INCIDENT_LOC_EVENTS_VW.INCD_WRONG_STRT_NAME};

//Remainder of code should work for all locations and/or addresses

// Destination
stringvar Location := &quot;&quot;;

if StNo <> &quot; &quot; then Location := StNo + &quot; &quot;;
if Str <> &quot; &quot; then Location := Location + Str + &quot; &quot;;
if Apt <> &quot; &quot; then Location := Location + &quot;#&quot; + Apt + &quot; &quot;;

If Wrongaddr1 <> &quot; &quot; then Location := Location + Wrongaddr1 + &quot; / &quot;;
If Wrongaddr2 <> &quot; &quot; then Location := Location + Wrongaddr2;

Location; [sig][/sig]

 

[kenhamady]

Try the following:

// Destination
stringvar Location := &quot;&quot;;

If Wrongaddr1 <> &quot; &quot; then Location := Wrongaddr1 + &quot; / &quot;;
If Wrongaddr2 <> &quot; &quot; then Location := Location + Wrongaddr2;

If stno + str + apt > ' ' then Location := '' ;

if StNo <> &quot; &quot; then Location := StNo + &quot; &quot;;
if Str <> &quot; &quot; then Location := Location + Str + &quot; &quot;;
if Apt <> &quot; &quot; then Location := Location + &quot;#&quot; + Apt + &quot; &quot;;

Location;


Doing the wrong address first allows it to be overwritten by the correct address. The reset eliminates the wrong address if there is a correct one [sig]<p>Ken Hamady-

http://www.kenhamady.com<br><a

href=

http://www.kenhamady.com/>Crystal

Reports Training by Ken Hamady</a><br>[/sig]