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
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
Dim m As String
m = "123456789"
Debug.MaskSSN(m)
Debug.Print MaskSSN(m, , , 2)
Debug.Print MaskSSN(m, , , 3)
Result:
1234*****
*****6789
12*****89
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.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
you can create a function in a Module and call it in the Query: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
'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
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.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
PMFJI,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.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!
SELECT LName, FName, "xxx-xx-xxxx" AS SSN FROM TableName
SELECT LName, FName, "xxx-xx-" & Right([SSN],4) AS TIN FROM TableName
'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