Format Number Expression

[Kath via AccessMonster.com]

Hi there! I am working on a system that talks to our PR db. This system
has a need for the SSN, however, the file we need to submit does not have
dashes and the PR system is capturing dashes in the data field. I do not
want to do an update query to remove the dashes, but want to use the
formatnumber expression to remove the dashes in my query - which will be
used as an export file to send to an outside organization. Can anyone help
me in this? I have tried:
Expr1: FormatNumber([EMPLOYEE]![E_SSN],"000000000") and Expr1: FormatNumber
([EMPLOYEE]![E_SSN],"000000000;;_") and neither works..I am missing
something....but not sure what. Any help would be GREATLY appreciated.

 

[Sunny]

select replace(e_SSN,"-","") AS n_SSN from yourtbale

 

[John Vinson]

Hi there! I am working on a system that talks to our PR db. This system
has a need for the SSN, however, the file we need to submit does not have
dashes and the PR system is capturing dashes in the data field. I do not
want to do an update query to remove the dashes, but want to use the
formatnumber expression to remove the dashes in my query - which will be
used as an export file to send to an outside organization. Can anyone help
me in this? I have tried:
Expr1: FormatNumber([EMPLOYEE]![E_SSN],"000000000") and Expr1: FormatNumber
([EMPLOYEE]![E_SSN],"000000000;;_") and neither works..I am missing
something....but not sure what. Any help would be GREATLY appreciated.

Is the data type of the E_SSN field Number (which would not be a very
good idea) or (more properly) Text?

If it's Text and the field actually contains the hyphens, you could
simply pick out the digits:

ExpSSN: Left([E_SSN], 3) & Mid([E_SSN], 5, 2) & Right([E_SSN], 4)


John W. Vinson[MVP]