Remove numbers from beginning of social security number

  • Thread starter Thread starter CMcGrann
  • Start date Start date
C

CMcGrann

I have a field that contains a social security number. Due to new privacy
laws, we can only display the last four positions. I want to replace the
first 5 characters with an asterisk, but leave the dashes and last four
numbers as is. Can this be done with an update query?

123-45-6789 I want to replace with ***-**-6789
 
Certainly!

Open a new query and change it to an update query.
Select the Social number field
In the 'update to' box put in

"****-***-" & Right([SSNum],4)

and change the field name!
 
The answers that scubadiver gave will change the actual values.

If all you want is to DISPLAY the values differently then on whatever
forms/reports that show the SSN change the txtbox to an unbound txtbox
and in the oncurrent event
me.txtshowssn = "****-***-" & Right(me.[SSNum],4)

or place = "****-***-" & Right(me.[SSNum],4)
in the record source for the txtbox.

Ron
 
STOP. An update query will REPLACE the data. Since you want to change the
display of the information, I would use an expression such as

"***-**-" & Right([SSN Field],4)

Better yet, the following will return a blank if SSN is blank (or null), while
the above will return "***-**-".

Format(RIGHT([SSN Field],4),"\*\*\*\-\*\*\-@@@@")



John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
Back
Top