Access function

  • Thread starter Thread starter Kirk P.
  • Start date Start date
K

Kirk P.

Is there a function I can use that will strip out the
dashes in a Social Security number?
 
Not that I know of, but you can do this pretty easily in
an expression by using the left(), mid() and right()
funtions to get each piece and concatenating them
together.

-Ted Allen
 
I'm getting an undefined function error. I'm using Access '97, and I don't see the Replace function as an option. Is there a way I can add that function

----- PRL wrote: ----

sNewSSN=Replace("Your-SSN","-")
 
-----Original Message-----
Is there a function I can use that will strip out the
dashes in a Social Security number?
.
'Add a field Called CleanSocialSecuritynumber in yout
Click to expand...
Table. Under HELP check ASCII characters
'This stripes the punctuation from the
SocialSecuritynumber in the Table "tblYOURNAME"
'& puts the stripped SocialSecuritynumber into the Field
CleanSocialSecuritynumber. If the field is empty then
'it updates the field CleanSocialSecuritynumber to null &
continues to the next SocialSecuritynumber
'As long as the Table & fields exist can be run straight
from a Module.
Private Sub cleanpart11()
Dim rstData As Recordset ' holds records to be
processed
Dim strTmpPart As String ' temp location for RefNo
being processed
Dim iloop As Integer ' loop counter, used to keep
track of position in string

'Turn Acces warnings off
DoCmd.SetWarnings False
'Opens data set for processing
Set rstData = CurrentDb.OpenRecordset
("tblYOURNAME") 'The name of the Table
While Not rstData.EOF
'checks for field 'SocialSecuritynumber'
containing a null
If Not IsNull(rstData.Fields
("SocialSecuritynumber")) Then
'if no null then process field
' get this record SocialSecuritynumber
strTmpPart = rstData.Fields
("SocialSecuritynumber")
iloop = 1
'for each character in SocialSecuritynumber
check for AlphaNumeric
While iloop <= Len(strTmpPart)
'if not an alphanumeric, remove from
string by taking all chars upto and all chars after
If Asc(Mid(strTmpPart, iloop, 1)) < 48 Or
(Asc(Mid(strTmpPart, iloop, 1)) > _
57 And Asc(Mid(strTmpPart, iloop, 1)) <
65) Or (Asc(Mid(strTmpPart, iloop, 1)) _
90 And Asc(Mid(strTmpPart, iloop, 1)) <
Click to expand...
97) Or Asc(Mid(strTmpPart, iloop, 1)) > 122 Then
strTmpPart = Left(strTmpPart, iloop - 1) &
Mid(strTmpPart, iloop + 1, Len(strTmpPart))
End If
'next character
iloop = iloop + 1
Wend
' store cleaned SocialSecuritynumber
in 'CleanSocialSecuritynumber' field of dataset
rstData.Edit
rstData.Fields("CleanSocialSecuritynumber") =
strTmpPart
rstData.Update

Else
'If the field is null then strTmpPart is null
End If
' get next record
rstData.MoveNext
Wend
' all records done - close dataset
rstData.Close
End Sub
 
I am giving you a beginners approach, but it worked for me.
I opened my tables in datasheet view, used find and replace from the edit
menu. Find -, replace left empty, replace all. It worked.
Then I reformatted using the input mask.
 
Annelie,
Sometimes the best approach is a new one. I think your idea was great.
Kirk,
Charlie and Ted's ideas also work, you will have to use the InStr fn along with the Left, Mid, and Right fns that Ted mentioned. The Replace function I mentioned works, but not in Access 97. It is located in a later version (6.3) of the VBA library. Access 2K on would be able to use it.
 
Back
Top