SSN Encoding

[Lunch]

I need to create a basic form that converts a SSN into a encoded format.

For example (NOTE these are not actual characters it’s just an
example…)


A=1
B=2
C=3
D=4
E=5
F=6
G=7
H=8
I=9
J=0
K=”-“

So, when someone enters 123-45-6789 into a form the code converts the
number to ABCKDEKFGHIJ and stores it in a table. Anyone know of some
example code on how to do this? I’m assuming it should be relatively
straight forward.

PS - Yes I realize that this isn't true encryption....yes I realize it has major
flaws....however at this point I'm just trying to replace a pearl program that did the exact same thing. Once I have this in place we'll look at improving the encryption......yes I know we could try and fix everything on one step BUT that requires decrypting and re-encrypting and getting people conformable with not being able to do this stuff by hand. One step at a time....

[blacksaibot]

Take the text and store it into a string. Go through the string character by character (with a loop) and use a switch statement on each character to determine what letter to change the number.

I don't know if VBA supports it, but look into "for each" loops.
So "for each" character in the string, do this (this referring to a function that holds the switch).

Just simply concatenate each character into a new string and insert/update the record into your table.


Pseudo code:

Dim newSSN as String = "" 
 
Dim curSSN as String = textbox.text
 
Dim i as Integer = 0
 
'length of a SSN is 11: ###-##-####
Dim length as Integer = 11
 
' length minus 1 since character count starts at 0
'charAt is JAVA's "character at position(i)" not sure about VBA/VB.NET
while (i <= length - 1)
    switch (curSSN.charAt(i)) 
    { 
        case "1":
         'if the character is 1, concatenate A
            newSSN = newSSN & "A"
 
        case "2":
         'if the character is 2, concatenate B            
           newSSN = newSSN & "B"
 
         ' etc. etc.
         'finish cases for 0 and 3 to 9
         ' etc. etc.
 
        default: 
     'if there is no number, must be a dash (-)
            newSSN = newSSN & "K"
        break; 
    } 
 
 'increment i by 1 to get to the next character position
    i = i + 1
 
end while

So now newSSN should be all letters of the alphabet.
Use that string in your query that you send to the database.

Hope I didn't screw up my code...

[RuralGuy]

I would assume you need both the encript and decript functions.

[Lunch]

At some point I'm sure we'll need to look into it but honestly at this point I think the data is stored "just in case."

[ChrisO]

Public Function ConvertSSNToAlpha(ByVal vntSSN As Variant) As Variant
    Dim blnJunk As Boolean
    Dim intI    As Integer
    Dim intChar As Integer
    Dim strTemp As String
    
    If Len(vntSSN) Then
        vntSSN = Replace(vntSSN, " ", "")
    
        For intI = 1 To Len(vntSSN)
            intChar = Asc(Mid$(vntSSN, intI, 1))
            
            If (intChar < 48 Or intChar > 57) And (intChar <> 45) Then
                blnJunk = True
                Exit For
            End If
            
            If intChar = 48 Then intChar = 58
            
            strTemp = strTemp & IIf(intChar = 45, "K", Chr$(intChar + 16))
        Next intI
    
        If Not (blnJunk) Then
            ConvertSSNToAlpha = strTemp
        End If
    End If

End Function

[Lunch]

Hi Chris,

Unfortunately the character encoding isn't straight letters to numbers I just tried to provide a simple example. I should have been more specific.....the real encoding is closer to this.

1=A
2=B
3=C
4=3
5=S
6=8
7=Z
8=X
9=U
0=G
"-"= J

[RuralGuy]

Can you figure out how to code this now or do you need additional assistance?

[Lunch]

I'm still working through it. The way I’m leaning towards substring from the text box, and using two arrays that contain the possibilities and the conversion, then save the results into the table. I’m just trying to work out the code to actually do that.

[RuralGuy]

Putting these two functions in a standard module and invoking the EncriptSSN Function from the BeforeUpdate event of your form should be a lot easier and *much* easier to maintain later on.

Public Function EncriptSSN(strIn As String) As String
'-- Encript the incoming SSN value
   Dim Marker As Long
   If Len(strIn & "") > 0 Then
      For Marker = 1 To Len(strIn)
         EncriptSSN = EncriptSSN & EncriptIt(Mid(strIn, Marker, 1))
      Next Marker
   Else
      '-- Do not attempt any conversion
      MsgBox "Invalid entry for 'EncriptSSN' ", vbExclamation + vbOKOnly
   End If
End Function
Public Function EncriptIt(InChr As String) As String
'-- Encript the incoming character to the following spec
'1=A, 2=B, 3=C, 4=3, 5=S, 6=8, 7=Z, 8=X, 9=U, 0=G, "-" = J
   Select Case InChr
      Case "1"
         EncriptIt = "A"
      Case "2"
         EncriptIt = "B"
      Case "3"
         EncriptIt = "C"
      Case "4"
         EncriptIt = "3"
      Case "5"
         EncriptIt = "S"
      Case "6"
         EncriptIt = "8"
      Case "7"
         EncriptIt = "Z"
      Case "8"
         EncriptIt = "X"
      Case "9"
         EncriptIt = "U"
      Case "0"
         EncriptIt = "G"
      Case "-"
         EncriptIt = "J"
      Case Else
         EncriptIt = ""        '-- Strip this character
   End Select
 
End Function

[Lunch]

Great, I'll give this a shot thanks!

[Lunch]

I created a basic database to test it out. I’ve placed the code into a general module Encryption. I created one single table (table1) that has a primary key called ID and a second variable called “SSN.” SSN is a general text field with a field size of 255. I created one form (form1) with the record source to table1. I dropped SSN onto the form. Next, I looked at the form property sheet and went to the event tab before update field. On this field, I choose expression builder. In expression builder, I choose Functions->New Microsoft Office Access Database -> Encryption -> EncriptSSN. This places “EncriptSSN («strIn»)” in the expression builder and I replace <<strIn>> with [SSN], which is the name of the object on form1 and the field in the table. In the end I end up with a “= EncriptSSN ( [SSN] )” minus the quotes in the before update field of the properties page. Unfortunately, it’s not encoding what I enter and I’m not sure why. I’ve looked through MS’s expression builder help and as far as I can tell I’ve done everything right so this should work. I’ve tried to set the expression on the object and the form and neither is working so……what the heck am I doing wrong? The <<strin> should be the name of the field right? *sigh*

[RuralGuy]

Attach your test db to a post here, please.

[Lunch]

RuralGuy - Attached is the db. Sorry for being such an idiot. I can code for this stuff in stats programs like SAS and SPSS but when I get into VB it's an adventure....

Thanks again for your help.

[RuralGuy]

There are some issues that you will have fun with but try this db.