Formatting a social security number 2

[ivalum21]

In my database, the social security numbers are in a currency format (not sure why, but they are). And in order to get the "-" in my data, I did the following:

Picture(Totext({R_Age19_Dep_Letter.EMPLOYEE_ID}, 0), "xxx-xx-xxxx")

But unfortunately, I still have the "$" and the commas seperating the thousands to worry about.

How do I get rid of the currency formatting and only have my social security formatting? Also, there are some social security numbers that having a leading zero...just an FYI.

Thanks for your help!

 

[mrudolph]

Try:

Picture(ToText(ToNumber({R_Age19_Dep_Letter.EMPLOYEE_ID}),0,''),'xxx-xx-xxxx')

 

[lbass]

Try the following:

picture(trim(replace(totext({table.$ssn},0,""),"$"," ")),"xxx-xx-xxxx")

For the replace function, I replaced the currency sign with a space and then used trim() so that the replace function would work accurately in 8.0, in case that is your version. In 8.0, the replace function does not properly replace a character with "". This formula should work in 8.0 or above.

-LB

 

[ivalum21]

Alright, both of your functions worked, but I still have the problem of the leading zero. For example, one of the social security numbers begins with a "0", and it is dropping off that first zero thus giving the social security number 8 digits instead of 9.

 

[mbarron]

Try this one:

picture(trim(replace(totext({ssn.Field},"000000000"),"$"," ")),"xxx-xx-xxxx")

Sidebar:
In version 8 the replace() error can be overcome by using the Cstr() function

cstr(replace({field},"X",""))



Mike

 

[ivalum21]

I am using v8.5, and when I use that function, I get an error message saying: "Too many arguments have been given to this function.

 

[mbarron]

Which formula?

Here is a direct paste of the formula I used. {@ssn}'s
value was $12345678 resulting in 012-34-5678.

picture(trim(replace(totext({@ssn},"000000000"),"$"," ")),"xxx-xx-xxxx")


Mike

 

[ivalum21]

mbarron you're a genius!!

Thank you very much, that worked perfect.

- N. Nolte

 

[lbass]

Thanks (*), Mike, for the solution to the problem with the replace function in 8.0.

-LB