Hidden Beginning Zero for Social Security# in Table 1

[kmkland]

I have formatted my table to include all 9-digits of a social security #, however, the beginning zeros for some social security #s are not included. How do I format my table to show these hidden zeros??
Regards,
Kim

 

[PHV]

If the SSN field is defined as text:
UPDATE yourTable SET [ssn field]=Format(Val([ssn field]), "000000000")
WHERE Len(Trim([ssn field] & "")) Between 1 And 8

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[kmkland]

Hi PH,
I attempted this update, but it gave a message stating that it was only updating 1 row... hmm.

UPDATE [emp list test] SET [emp list test].[SS#] = Format(Val([SS#]),"000000000")
WHERE (((Len(Trim([SS#] & " "))) Between 1 And 8));

I did this correct though, yea?
Regards,
Kim

 

[PHV]

Replace this:
Len(Trim([SS#] & " "
By this (as I posted):
Len(Trim([SS#] & ""

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[kmkland]

PH,Thanks so much!! It worked!!!!
Kindest Regards,
Kim