Mask SSN data in query output (1 Viewer)

TraceSL

Registered User.
Local time
Today, 16:11
Joined
Sep 12, 2017
Messages
51
Hello: Looking for a function to use in a query to mask the tax ID numbers and print on tax forms. Any suggestions? Appreciate the help! Tracy
 

moke123

AWF VIP
Local time
Today, 16:11
Joined
Jan 11, 2013
Messages
3,852
have you tried the password input mask?
 

Isaac

Lifelong Learner
Local time
Today, 13:11
Joined
Mar 14, 2017
Messages
8,738
Code:
Function RedactTaxID(strInput As String) As String
RedactTaxID = Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(strInput, "0", "*"), "1", "*"), "2", "*"), "3", "*"), "3", "*"), "4", "*"), "5", "*"), "6", "*"), "7", "*"), "8", "*"), "9", "*")
End Function
1611250903765.png
 

theDBguy

I’m here to help
Staff member
Local time
Today, 13:11
Joined
Oct 29, 2018
Messages
21,358
Hi. Can you show us what you expect to see as a masked SSN data? For example, did you still want to show the last 4 digits? Thanks.
 

isladogs

MVP / VIP
Local time
Today, 20:11
Joined
Jan 14, 2017
Messages
18,186
If you do want to hide all characters in the tax ID, you could just set the forecolor and backcolor properties of the textbox to be identical e.g. Black on black (redacted)
OR if you want to show say the last 4 characters in a 12 character string you could use ="********" & Right(TaxID,4)
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 16:11
Joined
Feb 19, 2002
Messages
42,973
Since users NEVER, EVER see queries, there is no need to mask the data in a query. You can mask it on the table and that will carry forward to the forms/reports. I generally have a toggle button. The form always opens with SSN hidden but toggling the button allows the user to see the SSN. I hide the SSN in the Current event of the form so for each new record the SSN is hidden unless the user specifically unhides it.

You can and probably should, assign password control to the toggle to limit those who can see the SSN. Usually only the data entry people actually need to see the SSN value.. You could have an unbound control with the last 4 digits if that helps. Code to populate the "last 4" needs to go in the form's Current event and in the AfterUpdate event of the SSN control. The last 4 should not be saved. That's why you need the code to populate it in two places.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:11
Joined
May 7, 2009
Messages
19,169
in case you want to put the mask on the Left or Right or Center, add
a function in a Module:
Code:
Public Function MaskSSN(ByVal SSN As Variant, _
                        Optional ByVal NumberOfCharToExpose As Integer = 4, _
                        Optional ByVal MaskChar As String = "*", _
                        Optional ByVal Direction As Integer = 1) As String
'arnelgp
'Note:
'
'Direction of mask can be 1=left, 2=right, 3=Middle, any other
'number will mask the whole field.
'
    Dim ln As Integer
    Dim md As Integer
    Dim t As String
    SSN = SSN & vbNullString
    ln = Len(SSN)
    If ln = 0 Then Exit Function
    If Direction < 1 Or Direction > 3 Then
        MaskChar = String$(ln, MaskChar)
    End If
    If Len(MaskChar) > ln Then _
        MaskChar = left$(MaskChar, ln)
    If Len(MaskChar) = ln Then
        MaskSSN = MaskChar
        Exit Function
    End If
    MaskChar = String$(ln - NumberOfCharToExpose, MaskChar)
    md = Len(MaskChar) \ 2
    Select Case Direction
        Case Is = 1
            MaskChar = left$(SSN, ln - Len(MaskChar)) & MaskChar
        Case Is = 2
            MaskChar = MaskChar & right$(SSN, ln - Len(MaskChar))
        Case Else
            t = left$(SSN, (ln \ 2) - md) & MaskChar & right$(SSN, (ln \ 2) - md)
            If Len(MaskChar) < ln Then
                t = left$(SSN, (ln \ 2) - md) & MaskChar & right$(SSN, (ln \ 2) - md + 1)
            End If
            MaskChar = t
           
    End Select
    MaskSSN = MaskChar
End Function

test:

Code:
Dim m As String
m = "123456789"
Debug.MaskSSN(m)
Debug.Print MaskSSN(m, , , 2)
Debug.Print MaskSSN(m, , , 3)


Result:

1234*****
*****6789
12*****89

In Query:

Select maskssn([ssn],4,"*",2) As Expr1 From yourTable;
 
Last edited:

onur_can

Active member
Local time
Today, 13:11
Joined
Oct 4, 2015
Messages
180
No need to work hard, the simplest is to use the input mask and save it to the table as an input mask. If this happens, the report outputs will appear exactly as entered.
 

TraceSL

Registered User.
Local time
Today, 16:11
Joined
Sep 12, 2017
Messages
51
Thanks everyone for the great suggestions. The masking is in accordance to the US IRS rules. At this time I am just opening the table with the print data and using simple find and replace, replacing all numbers with an X while retaining the format of the TaxIDs, which varies dependent on the entity type. This is the easiest quickest way to get the data formatted to the IRS rules. Tracy
 

theDBguy

I’m here to help
Staff member
Local time
Today, 13:11
Joined
Oct 29, 2018
Messages
21,358
Thanks everyone for the great suggestions. The masking is in accordance to the US IRS rules. At this time I am just opening the table with the print data and using simple find and replace, replacing all numbers with an X while retaining the format of the TaxIDs, which varies dependent on the entity type. This is the easiest quickest way to get the data formatted to the IRS rules. Tracy
Hi Tracy. Just curious, what is this "IRS rules?" Can you show us? If you are going to the table and updating the SSN data, are you saying you are erasing what was in there before? I think it was already mentioned that rather than using/opening the Table, you could try using a Query where you can mask the SSN and still retain their values.
 

plog

Banishment Pending
Local time
Today, 15:11
Joined
May 11, 2011
Messages
11,611
This makes no sense. Either you want the data or you don't.

Since you don't--don't even bring the SSN field into the query. Non-Problem solved.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:11
Joined
May 7, 2009
Messages
19,169
At this time I am just opening the table with the print data and using simple find and replace, replacing all numbers with an X while retaining the format of the TaxIDs, which varies dependent on the entity type. This is the easiest quickest way to get the data formatted to the IRS rules. Tracy
you can create a function in a Module and call it in the Query:
Code:
'arnelgp
Public Function MaskSSN(ByVal sText As Variant, Optional ByVal MaskChar As String = "X") As String
    sText = sText & vbNullString
    If Len(sText) < 1 Then Exit Function
    With CreateObject("VBScript.RegExp")
            .Pattern = "([\d])"
        .Global = True
        .IgnoreCase = True
        .MultiLine = True
        sText = .Replace(sText, MaskChar)
    End With
    MaskSSN = sText
End Function

On your Query:

Select [first name], [last name], MaskSSN([SSN], "X") As MaskedSSN From yourTableName;
 

TraceSL

Registered User.
Local time
Today, 16:11
Joined
Sep 12, 2017
Messages
51
Hello: Thanks for all the feedback, sorry for the delay this deadline looming with a variety of regulatory reporting changes related to COVID.
The TIN (taxpayer identification number aka social security number or employer identification number) is in the table because it's used for other reporting requirements however the hard copy printed form sent to the recipient allows for the TIN to appear in the proper format related to the specific TIN (format may vary) masked. Since the timeline tight on meeting reporting obligation we are simply using the find and replace feature with table open to mask TIN and print. I'll be sweeping back to code to see if I'll add logic for masking no time for it at this point! Tracy
 

theDBguy

I’m here to help
Staff member
Local time
Today, 13:11
Joined
Oct 29, 2018
Messages
21,358
Hello: Thanks for all the feedback, sorry for the delay this deadline looming with a variety of regulatory reporting changes related to COVID.
The TIN (taxpayer identification number aka social security number or employer identification number) is in the table because it's used for other reporting requirements however the hard copy printed form sent to the recipient allows for the TIN to appear in the proper format related to the specific TIN (format may vary) masked. Since the timeline tight on meeting reporting obligation we are simply using the find and replace feature with table open to mask TIN and print. I'll be sweeping back to code to see if I'll add logic for masking no time for it at this point! Tracy
Hi Tracy. I have asked for it a couple of times already; but if you could provide us with an example of the mask you require, we may be able to give you a query that will do the work for you, without needing to use find and replace or any VBA code.
 

TraceSL

Registered User.
Local time
Today, 16:11
Joined
Sep 12, 2017
Messages
51
The TIN format varies but typically either xx-xxxxxx or xxx-xx-xxxx
however the source system does not validate the data entered into this field
therefore the query would need to look at the field and replace numbers with an X and leave dashes where the exist to retain the format.

Hope this helps!
 

Gasman

Enthusiastic Amateur
Local time
Today, 20:11
Joined
Sep 21, 2011
Messages
14,047
The TIN format varies but typically either xx-xxxxxx or xxx-xx-xxxx
however the source system does not validate the data entered into this field
therefore the query would need to look at the field and replace numbers with an X and leave dashes where the exist to retain the format.

Hope this helps!
PMFJI,
Have you tried arnelgp's solution in post #12?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 13:11
Joined
Oct 29, 2018
Messages
21,358
The TIN format varies but typically either xx-xxxxxx or xxx-xx-xxxx
however the source system does not validate the data entered into this field
therefore the query would need to look at the field and replace numbers with an X and leave dashes where the exist to retain the format.

Hope this helps!
Hi. Thanks for trying to grant my request, but I don't think we are understanding each other. If all you need to see on the report is xxx-xx-xxx, then you don't even need to worry about replacing any numbers, because you can simply use that. For example, here's one possible query.
SQL:
SELECT LName, FName, "xxx-xx-xxxx" AS SSN FROM TableName
Otherwise, I was hoping you would show us something like this:

Table Data: 123-45-6789
Report Data: xxx-xx-6789

In that case, I would say use something like this.
SQL:
SELECT LName, FName, "xxx-xx-" & Right([SSN],4) AS TIN FROM TableName
Hope that makes sense...
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:11
Joined
May 7, 2009
Messages
19,169
here is another variation, it will Mask Numbers and Letters except (-/ \;., etc.):
Code:
'arnelgp
Public Function MaskSSN2(ByVal sText As Variant, Optional ByVal MaskChar As String = "X") As String
    sText = sText & vbNullString
    If Len(sText) < 1 Then Exit Function
    With CreateObject("VBScript.RegExp")
            .Pattern = "([0-9a-zA-Z])"
        .Global = True
        .IgnoreCase = True
        .MultiLine = True
        sText = .Replace(sText, MaskChar)
    End With
    MaskSSN2 = sText
End Function
 

Users who are viewing this thread

Top Bottom