Formatting Social Security Numbers

[NMKEP]

In my database, I have SS#'s listed as complete 9 digit numbers. Is there a
way to suppress all but the last 4 numbers? Another words, when I add a new
employee, use all 9 nuumbers, but only have the last 4 show up on the Form
screen or in reports, unless I specifically need to have all 9 available?

 

[Jerry Whittle]

Use the Right function. In a query it would look like so:

LastFour: Right([SS#],4)

Make sure to put in the correct field name.

 

[John Spencer]

Personally, I would probably split the SSN into SSNFirst5 and SSNLast4
and combine the results when I needed the entire value. That way I
could show just the last 4 when required.

If that doesn't work for you, then use Right(SSN,4) to get the last 4
digits. If you want to show placeholders then
"*****" & Right(SSN,4)

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Clifford Bass]

Hi,

Note that hiding it on the form does not prevent the viewing of the
whole SSN through some other means. Here is a way to provide it on a form so
that it can be viewed in whole if needed, otherwise it will only show as
***-**-nnnn: Create an unbound text box names something like txtSSN.
Include this code in your form's code window:

Private Const m_cstrSSNAll9 As String = "SSN" ' The SSN field name
Private Const m_cstrSSNLast4 As String = _
"=IIf(IsNull([SSN]), Null, ""***-**-"" & Right([SSN],4))"

Private Sub Form_Open(Cancel As Integer)

[txtSSN].ControlSource = m_cstrSSNLast4

End Sub

Private Sub txtSSN_GotFocus()

[txtSSN].ControlSource = m_cstrSSNAll9

End Sub

Private Sub txtSSN_LostFocus()

[txtSSN].ControlSource = m_cstrSSNLast4

End Sub

Now when the form it opened, as long as txtSSN is not the first
control, only the last four digits will show. But if someone needs to see
all of the digits and/or enter/update them, when the person clicks in the
field, it will display all of the digits and allow for them to be edited and
saved.

Clifford Bass