Pulling SSN field but field format varies

[pokerace]

C10
ACT! 2000

Perhaps this belongs in the formulas forum?

I have created a subreport that pulls data from an ACT database linked by SSN to the main report. It appears that some of the SSN numbers in the ACT database are of two formats: 123-45-6789 and 123456789

The SSN fields from the main report only includes the dashes in the SSN, so the subreport will only return data of that format and skip SSNs that don't include the dashes. Is there a way to convert the all of the SSNs to include dashes using Crystal so that I don't have to mess with the database fields?

 

[pokerace]

I was able to use the following formula to make the conversion and link the subreport based on the formula field, but for some reason it still does not pull the records of the newly converted SSN field. The report will use the SSNs of size 11 however, so I know that the formula is being used.

if Length ({Contact.Soc Sec No}) <> 11 then
Left ({Contact.Soc Sec No},3)+"-"+Mid ({Contact.Soc Sec No},3,2)+"-"+Right ({Contact.Soc Sec No},4)
else
{Contact.Soc Sec No}

 

[pokerace]

Get it working. There was an error on the conversion formula.