Find and Move Sensitive Data 1

[alr0]

Hi,

I looking for the best way to find and then move and/or delete social security numbers. I would be looking for:

###-##-####
##-#######
#########

This will also include a search for Account numbers, so I am thinking all digits that follow "Acct" to the following space.

This will be VBA vs a query so there is fine control over what happens to the text. I can probably handle the text manipulation but I am not sure if it would be best to use instr, find, or something else.

Thanks for any input,

alr

_____________________________________
If a large part of intelligence is the ability to generalize, it is ironic that a large part of what we call wisdom is the ability not to generalize.

 

[PHV]

what about the Like operator ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[alr0]

My plan was to use the instr command but I cannot get the instr to work with the like operator.

If InStr([rsRmvSensInfo]![NAME1],like "*#######*") Then

This line is not valid

Is there another way to search a text string using the like operator.

Thanks,

alr

_____________________________________
If a large part of intelligence is the ability to generalize, it is ironic that a large part of what we call wisdom is the ability not to generalize.

 

[CautionMP]

One of these is not [navy][tt]Like[/tt][/navy] the other...

If [rsRmvSensInfo]![NAME1] Like "*###-##-####*" Or _
   [rsRmvSensInfo]![NAME1] Like "*##-#######*" Or _
   [rsRmvSensInfo]![NAME1] Like "*#########*" Then
  [green]'Do something here[/green]
End If

CMP

(GMT-07:00) Mountain Time (US & Canada)

 

[alr0]

Thanks CautionMP,

That will certainly identify the fields that need attention but to move that data elsewhere and remove it while leaving the remainder of the field unaltered I need the starting point.

I guess I can loop through each field's contents but that seems absurdly time consuming. Is it the only way?

Thanks,

alr

_____________________________________
If a large part of intelligence is the ability to generalize, it is ironic that a large part of what we call wisdom is the ability not to generalize.

 

[CautionMP]

The loop is the only way I can think of right now, but it's only Monday

You could use something like this as a starting point. I wrote it as a function so you could use it as the control source for a field in a form, or as the new value for an update query. Since there are so many options on how/where to 'move' the data I left a [tt]Debug.Print[/tt] line as a place holder in the code. Depending on how big [tt]YourTextField[/tt] is you may not need to manipulate the data, just use this function to mask out the sensitive stuff.

Function ReplaceSSN(YourFieldText As String) As String
Dim intCounter As Integer, intCharacters As Integer
Dim intPatternsToCheck As Integer
Dim strPatterns(2) As String
Dim strSubText As String
'Define the patterns
strPatterns(0) = "###-##-####" '1
strPatterns(1) = "##-#######"  '2
strPatterns(2) = "#########"   '4

'Perform the checks
For intCounter = 0 To UBound(strPatterns)
  'Test the entire string first
  If YourFieldText Like "*" & strPatterns(intCounter) & "*" Then
    'The pattern was found, find the occurance
    For intCharacters = 1 To (Len(YourFieldText) - Len(strPatterns(intCounter))) + 1
      strSubText = Mid(YourFieldText, intCharacters, Len(strPatterns(intCounter)))
      If strSubText Like strPatterns(intCounter) Then
        'The pattern was found, output the value
        'You could add your routine to capture the data here
        Debug.Print "Replaced Text:", strSubText
        'Replace the found text with the mask
        YourFieldText = Replace(YourFieldText, strSubText, strPatterns(intCounter))
        Exit For
      End If
    Next intCharacters
  End If
Next intCounter
ReplaceSSN = YourFieldText
End Function

If you want to remove the number from the original text and store it somewhere else, post back with the details of where you want to move the number to (another field, another table, a text file...).

Hope this helps,
CMP

(GMT-07:00) Mountain Time (US & Canada)

 

[alr0]

Thanks everyone for your assistance,

This seems cumbersome but perhaps the only way in VBA. Another possibility might be using regular expressions. Would that be a lot faster? Easier or harder to code? (not including the learning time) Any other possibilities?

Thanks again,

alr

_____________________________________
If a large part of intelligence is the ability to generalize, it is ironic that a large part of what we call wisdom is the ability not to generalize.

 

[RoyVidar]

I've been thinking about thread705-999357 since you started this thread.

Are you looking to both retrieve the account numbers, social security numbers ... and remove them from the field?

I think you'll only be able to do the replace through a query - and I suppose you'll need a VBA function for that too.

Anyway, here's a small attempt at creating a function that should return an array containing the string in the first position (where account numbers..., if found are replaced with vbNullString), and the different numbers in the following positions.

I doubt this is the most effective use of regexp, and I wouldn't know whether it is faster than the char by char approach. Usually the char by char approach is faster on shorter strings, but will be significantly slower as the searched text increases in length. Regexp has some overhead when initializing, but is less influenced by text length, and will therefore probably outperform the char by char approach on longer strings. You'll need to find out what is more suitable for your purposes.

[tt]Private mre As Object
' Declare as private (or public) within the module to
' to avoid reinstantiation per each usage

Public Function ExtractAndReplace(ByVal v_strIn As String) As String()

Dim m As Object
Dim mc As Object
Dim lngCount As Long
Dim tmpArr() As String

If (mre Is Nothing) Then
Set mre = CreateObject("vbscript.regexp")
mre.Global = True
mre.IgnoreCase = True
mre.Pattern = _
"(acct# \d+)|(\d{2}-\d{3}-\d{4})|(\d{2}-\d{7})|(\d{9})"
End If
If (mre.test(v_strIn)) Then
Set mc = mre.Execute(v_strIn)
If (mc.Count > 0) Then
lngCount = 0
ReDim tmpArr(mc.Count + 1)
For Each m In mc
lngCount = lngCount + 1
tmpArr(lngCount) = m.Value
Next m
set m = Nothing
Else
ReDim tmpArr(0)
End If
tmpArr(0) = mre.Replace(v_strIn, vbNullString)
set mc = Nothing
Else
ReDim tmpArr(0)
tmpArr(0) = v_strIn
End If

ExtractAndReplace = tmpArr

End Function

Sub TestIt()
Dim myarr() As String
Dim strString As String
Dim lngCount As Long

strString = <test string>
myarr = ExtractAndReplace(strString)
For lngCount = 0 To UBound(myarr)
Debug.Print myarr(lngCount)
Next lngCount

End Sub[/tt]

Roy-Vidar