Formatting an Access numeric field as Social Security Number 2

[VB400]

I have a numeric field defined in an Access97 table. This field contains Social Security Numbers. How do I format the data in my report so that it prints ###-##-#### rather than ###,###,###


Tarek

The more I learn, the more I need to learn!

 

[kenhamady]

Try this formula using your field:

Picture( Totext( {yourfield} , 0 , '' ) , "xxx-xx-xxxx" )

Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training and a
Quick Reference Guide to VB/Crystal

 

[VB400]

Ken,

I can't seem to find the Picture function. When I click Check, I get the message "The remaining text does not appear to be part of the formula." and the cursor goes to the beginning of the formula.

Picture (ToText ({ado.EmployeeID},0 ),"xxx-xx-xxxx" )


Tarek

The more I learn, the more I need to learn!

 

[MalcolmW]

Picture is a recent feature - and a nice addition.
A more cumbersome method:

StringVar EmpID := ToText({ado.EmployeeID},0,"" ) ;
EmpID[1 to 3] + "-" + EmpID[4 to 5] + "-" + EmpID[6 to 9]

Note that the third parameter for the ToText function is the comma separator to use - in this case empty string. This function defaults to using the Windows default comma separator.
The same applies to the second parameter - the number of decimal places - 0 in this case. Malcolm
wynden@telus.net

 

[VB400]

Malcolm,

I'm using the CR version that comes with VB6. The help file has reference to the Picture command but it doesn't seem to exist in the product itself.

As for your solution, unfortunately some numbers start with zero (I'm not sure if that's a valid social security number) but anyway, this formula doesn't like the subscripts used as the length becomes 8 and not 9.


Tarek

The more I learn, the more I need to learn!

 

[MalcolmW]

Interesting.
You could always pad (9 - length) zeros onto the beginning of the string.

StringVar EmpID := ToText({ado.EmployeeID},0,"" ) ;
EmpID := ReplicateString ("0", 9 - Length(EmpID)) + EmpID ;
EmpID[1 to 3] + "-" + EmpID[4 to 5] + "-" + EmpID[6 to 9]

Malcolm
wynden@telus.net

 

[kenhamady]

The ToText function allows you to indicate a zero or space pad length (use # for spaces). So you could do it this way also:

StringVar EmpID := ToText({ado.EmployeeID},"000000000",0,"" ) ;

EmpID[1 to 3] + "-" + EmpID[4 to 5] + "-" + EmpID[6 to 9]

Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training and a
Quick Reference Guide to VB/Crystal

 

[MalcolmW]

I'm not sure ToText(x,y,z,w) is supported in v4.6, but if it works, then it is a neater solution. Thanks, Ken
Malcolm
wynden@telus.net

 

[kenhamady]

Oops, forgot about the version.
Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training and a
Quick Reference Guide to VB/Crystal