Change Table Field Number to Text

[Guest]

I have a database at work I inherited which uses SSN as the primary field in
a table, stored as a number.

I'd like to change it to a text field without losing any data. When I tried
this on a copy of the db, everyone's SSN that started with 0 lost the zeros.
The number then gets stored as if it moved to the left, so when you view the
SSN it goes from
00abcdefg to
abcdefg

 

[Guest]

Use an update query to update the SSN field to:

Format([SSN],"000000000")

This will put in the leading zeros.

KenSheridan
Stafford, England

 

[missinglinq via AccessMonster.com]

I'm sure there's a number of ways to do this, but assuming that:

(1)you're using the table with the field already converted to text (where
you've lost the leading zeros)

(2) the SSNs were entered without the hyphens (I assume this since they were
originally numbers)

on the form displaying the SSNs:

Private Sub Form_Current()
If Len(Me.SSN) = 8 Then Me.SSN = "0" & Me.SSN
If Len(Me.SSN) = 7 Then Me.SSN = "00" & Me.SSN
End Sub

231706623 will still be 231706623

31706623 will become 031706623

1706623 will become 001706623

 

[Guest]

If you open the old table, and the SSN has leading zero's it can be:
1. The type of the field is already text, numeric fields can't have leading
zero's
2. In the field format property there is 0000000, that make leading zero's
apear.
If that the case, you can add the format to the new table

 

[Guest]

Thank you all so much!

If you open the old table, and the SSN has leading zero's it can be:
1. The type of the field is already text, numeric fields can't have leading
zero's
2. In the field format property there is 0000000, that make leading zero's
apear.
If that the case, you can add the format to the new table