Parse Out "-" in SSN Field

[mtech1]

Access 2000

I have a text field that data was imported into. Now I have a field
that has SSN's in different formats. (123-45-6789 and 123456789)

What I would like to do is to update the field removing all "-".
Any And All Help Truely Appreciated!
Dale

[fredg]

On 7 Dec 2004 16:25:11 -0800, mt****@yadtel.net wrote:

Access 2000

I have a text field that data was imported into. Now I have a field
that has SSN's in different formats. (123-45-6789 and 123456789)

What I would like to do is to update the field removing all "-".
Any And All Help Truely Appreciated!
Dale

It depends upon which version of A2000 you are using.
Use the Replace Function in a update query.

Update YourTable Set YourTable.[SSN] = Replace([SSN],"-","");

Note: Some early versions of Access 2000 have the Replace function but
it cannot be used directly in a query. If that is the case in your
instance, create a user defined query in a Module:

Public Function RemoveHyphen(StringIn)
RemoveHyphen = Replace(StringIn,"-","")
End Function

Call it from the Update query:
Update YourTable Set YourTable.[SSN] = RemoveHyphen([SSN]);

--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.

[Dave M]

Replace() should do it:

Replace("123-45-6789","-","") returns "123456789"

<mt****@yadtel.net> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

Access 2000

I have a text field that data was imported into. Now I have a field
that has SSN's in different formats. (123-45-6789 and 123456789)

What I would like to do is to update the field removing all "-".
Any And All Help Truely Appreciated!
Dale