Help -I need to take out dashes in SSN

[skd2726]

I have a field that was imported from Excel - I need to compare with another field - the one imported from Excel has dashes and the existing field does not. I know that I can use a trim statement, but I can't get it to work:

here's an example

is: 123-45-6789

needs to be: 123456789

Can anyone help me!

 

[RacerGirl117]

Did you import a complete Excel spreadsheet as a table in your database? You may be able to go into that table's design view, select the field you want to look at and then look to see if there is an input mask in that field.

In the spreadsheet, are the cells with SSN's formatted or are they just general text? The reason I ask is because I just did this to see what would happen. I created a spreadsheet with some sample data and formatted the cells to be formatted for SSN's. When I imported the table in and looked at the design view of the table, the field was imported as a number field and no input mask. Jessica Morgan
Fire Fighter Sales & Service Co.
Pittsburgh, PA

 

[GKProgrammer]

Hi skd2726,

I have created this function below to strip out any character out of a string, pass in the string and the character that you wish to be stripped out of the string, In your case Value = StripChr("123-456-789", "-&quot


Public Function StripChr(strValue As String, strChr As String) As String
Dim leng As Integer, strPos As Integer, chrPos As Integer

strPos = 1
leng = Len(strValue)
Do While 1 = 1
chrPos = InStr(strPos, strValue, strChr, 1)

If chrPos = 0 Then
StripChr = StripChr & Mid$(strValue, strPos, ((leng + 1) - strPos))
Exit Do
Else
StripChr = StripChr & Mid$(strValue, strPos, (chrPos - strPos))
End If
strPos = chrPos + 1
Loop

End Function


Let me know if this helps. Regards,
gkprogrammer

 

[skd2726]

Well, I'm too much of a beginner for this, I put the function in the Access module, and the formual in the query, but when I run it I don't get any results at all - what am I doing wrong?

 

[skd2726]

Figured out a "beginner" way to do it - I just changed the input mask on the field with no dashes to include dashes and then I ran the query to compare the 2 fields...thanks for all the help, I would like to know exactly how to do it the correct way if someone doesn't mind taking the time to explain it to me...I understand the code and would have no trouble with it in VB, I just don't know how to put it together in Access.

 

[JeremyNYC]

Methinks youthinks too much.

What's wrong with opening the form and doing a find-and-replace (ctrl-h) to get rid of all the dashes in that field?

Jeremy =============
Jeremy Wallace

http://www.AlphaBetCityDataworks.com

Designing, Developing, and Deploying Access databases since 1995.

 

[RacerGirl117]

Duh...that'd be the EASY way out. Who wants that? Jessica Morgan
Fire Fighter Sales & Service Co.
Pittsburgh, PA

 

[skd2726]

Well, I am guilty of doing things the hard way, but this time I ended up just writing a Trim statement to get rid of all the dashes and it worked just fine!

Thanks to everyone for the tips.....