Print Page | Close Window

Social security number formatting

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2022
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=17131
Printed Date: 19 Apr 2025 at 1:00pm


Topic: Social security number formatting
Posted By: TorLang
Subject: Social security number formatting
Date Posted: 26 Jul 2012 at 8:54am
here is the scenario:
In the database (hospital patient data) I am running report against, the social security numbers coming out of the table are in this format:

230,901,438 (I know, its insane. The field is also a number)
which after applying this:
Picture (ToText ({TSM040_PERSON_HDR.soc_scu_no}, 0, ""), "xxx-xx-xxx")

comes out nicely as 230-90-1438

Here is the problem though: If the SSN starts with a zero, it looks like this:
70,401,384

which translates to (after applying the formatting):
704-01-384 which is not good.

So my problem is how do I format this thing if it starts with a zero, or two zero's? Any ideas would be much appreciated.


Replies:
Posted By: lockwelle
Date Posted: 26 Jul 2012 at 9:10am
local stringvar x = right("00" + totext({table.field}, 0, ""), 10)
picture(x, "xxx-xx-xxxx")
 
should work

Posted By: TorLang
Date Posted: 26 Jul 2012 at 9:22am
local stringvar x = right("00" + totext({TSM040_PERSON_HDR.soc_scu_no}, 0, ""), 10)
picture(x, "xxx-xx-xxxx")

Get an error that point out that:
picture(x, "xxx-xx-xxxx")

is not part of the formula. I am looking for a missing bracket or paranthesis, but don't see any. Any suggestion...

Posted By: DBlank
Date Posted: 26 Jul 2012 at 9:22am
if for some reason lockwelle's answer does not cover it all this should handle it even if it is all all zeros...
 
picture(totext({TSM040_PERSON_HDR.soc_scu_no},'000000000',0,''),'xxx-xx-xxxx')
 

Posted By: lockwelle
Date Posted: 26 Jul 2012 at 9:24am
put a ; after the first line...silly me
sorry bout that

Posted By: TorLang
Date Posted: 26 Jul 2012 at 10:04am
yep that did it. Wished I had 5 years of Crystal Coding experience instead of 5 months. Thanks a lot, guys, for the quick replies...


Print Page | Close Window