I NEED HELP REMOVING THE DASHES!!!!!

[chubby]

I have a file (in Excel format) the contains 8852 records. I imported the file into my Access 97 database. Is there anyway to remove the dashes in the Social Security data field without going to each record and deleting them?

HELP...

 

[JerryDennison]

Use an Update query and the following formula:

FixedSSN: Left([SSN], 3) & Mid([SSN], 5, 2) & Right([SSN], 4)

 

[BoxHead]

If you'd like, here's function that can be used to replace characters.

Public Function StripChar(MyChar As String, _
    MyString As Variant, Optional NewChar As String) As String

Dim NewVal As String
NewVal = Nz(MyString, "There is no text to evaluate.")

    Do
    If InStr(NewVal, MyChar) = 0 Then
        Exit Do
    Else
        NewVal = Left(NewVal, InStr(NewVal, MyChar) - Len(MyChar)) _
        & NewChar & Mid(NewVal, InStr(NewVal, MyChar) + Len(MyChar))
    End If
    Loop
    
StripChar = NewVal

End Function

Paste it into a Module of it's own and then use
StripChar("-",[SSN])
in the query.


HTH

John

Use what you have,
Learn what you can,
Create what you need.

 

[chubby]

I'll try them both and let you both know the out come
Thanks a million...

 

[chubby]

I can't either one of these to work.

 

[JerryDennison]

What is the problem you have? Are you getting an error message? Make sure you use your actual field names for any placeholders given.

 

[sbohman]

Open the table with the social security number. Choose Replace from the Edit menu. In the dialog box type the single dash - in the Find text box. Leave the Replace With text box empty. Make sure the Look In box is set to the correct field. Set the Match box to Any Part of Field. Click Find Next, then click Replace. It will remove the dash. You can click Replace All once you are sure you are doing the right thing.

I use this frequently to clean up the () and - from phone numbers. You can also do this in excel prior to importing the data. It will also remove spaces when needed. Just type a space in the find box.

 

[chubby]

THANKS A MILLION JerryDennison IT WORKED....