Removing dashes and social security format in Access

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an Excel table with SSN format. I want to import it into Access then
delete the format making it plain text. There was a tech posting how to do
this but I lost it and would like to not have to pay another $40 to remeber
how ;-) Thanks for any help
 
Dave,
Run an Update query against the imported table field.
I'd create a new fieil in that table called TempSSN, and update that field. In case of
an error in updating, your original Excel values will still be in your original import
field. (ex. SSN)...

=Left(SSN,3) & Mid(SSN,5,2) & Right(SSN,4)

--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."
 
Hi Al, Dave:

Would the replace function work here? For example:
txtSSN=Replace("txtSSN","-","")

Cheers!
Fred Boer

Depending on your version of Access you could use the Replace function
 
Fred,
Excellent... I am just so used to using srtring functions, I never remember the Replace
method.
Thanks for the reminder...
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."
 
Thank you very much

Al Campagna said:
Dave,
Run an Update query against the imported table field.
I'd create a new fieil in that table called TempSSN, and update that field. In case of
an error in updating, your original Excel values will still be in your original import
field. (ex. SSN)...

=Left(SSN,3) & Mid(SSN,5,2) & Right(SSN,4)

--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."
Click to expand...
 
Thanks very much

Fred Boer said:
Hi Al, Dave:

Would the replace function work here? For example:
txtSSN=Replace("txtSSN","-","")

Cheers!
Fred Boer

Depending on your version of Access you could use the Replace function
Click to expand...
 
Hi -

Function ChopIt(pstr As String, ParamArray varmyvals() As Variant) As String
'*******************************************
'Purpose: Remove a list of unwanted
' characters from a string
'Coded by: raskew
'Inputs: From debug window:
' ? chopit("123-45-6789", "-")
'Output: 123456789
'*******************************************

Dim strHold As String
Dim I As Integer
Dim n As Integer

strHold = Trim(pstr)
'check for entry
If UBound(varmyvals) < 0 Then Exit Function
For n = 0 To UBound(varmyvals())
Do While InStr(strHold, varmyvals(n)) > 0
I = InStr(strHold, varmyvals(n))
strHold = Left(strHold, I - 1) & Mid(strHold, I + Len(varmyvals(n)))

Loop
Next n
ChopIt = Trim(strHold)

End Function

HTH - Bob

Dave said:
Thank you very much
Dave,
Run an Update query against the imported table field.
Click to expand...
[quoted text clipped - 8 lines]
Click to expand...
 
Back
Top