Remove numbers from beginning of social security number

[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

 

[scubadiver]

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!

 

[scubadiver]

***** Run it on a copy first to make sure it has updated correctly *****

 

[Ron2006]

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

 

[John Spencer]

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