We help IT Professionals succeed at work.

How to scramble and descramble data in fields

Juan Velasquez
on
Hello,

I've created the attached function to scamble an input string.  When I execute it, it works fine as seen in the following
?encrypt("tobeornottobe")
Original Member ID:  tobeornottobe
New Member ID:       ebottonorebot
Decrypted Member ID:  tobeornottobe

I was thinking that if I ran the code again and passing the now scrambled string, I could recover the original string.  As you can see, that's not happening.  I'm trying to see where I am going wrong.  It's probably something simple but I'm too close to the trees to see the forest
?encrypt("ebottonorebot")
Encrypted Member ID:  ebottonorebot
Original ID:       tobeeontotobr

Public Function Encrypt(strMemberID As String) As String
Dim strA As String
Dim strB As String
Dim strC As String
Dim strD As String
Dim strE As String
Dim strF As String
Dim strG As String
Dim strH As String
Dim strI As String
Dim strJ As String
Dim strK As String
Dim strL As String
Dim strM As String
Dim strN As String
Dim strO As String
Dim strP As String
Dim strQ As String
Dim strR As String
Dim strS As String
Dim strT As String
Dim strU As String
Dim strV As String
Dim strW As String
Dim strX As String
Dim strY As String
Dim strZ As String
Dim strAA As String
Dim strAB As String
Dim strAC As String
Dim strAD As String
Dim strAE As String
Dim strAF As String
Dim strAG As String
Dim strAH As String
Dim strAI As String
Dim strAJ As String
Dim strAK As String
Dim strAL As String
Dim strAM As String
Dim strAN As String
Dim strAO As String
Dim strAP As String
Dim strAQ As String
Dim strAR As String
Dim strAS As String
Dim strAT As String
Dim strAU As String
Dim strAV As String
Dim strAW As String
Dim strAX As String
Dim strAY As String
Dim strAZ As String
Dim strBA As String
Dim strBB As String
Dim strBC As String
Dim strBD As String
Dim strBE As String
Dim strBF As String
Dim strBG As String
Dim strBH As String
Dim strBI As String
Dim strBJ As String
Dim strBK As String
Dim strBL As String
Dim strBM As String
Dim strBN As String
Dim strBO As String
Dim strBP As String
Dim strBQ As String
Dim strBR As String
Dim strBS As String
Dim strBT As String
Dim strBU As String
Dim strBV As String
Dim strBW As String
Dim strBX As String
Dim strBY As String
Dim strBZ As String


Dim strNewMemberID

strA = Nz(Mid(strMemberID, 1, 1), "*")
strB = Nz(Mid(strMemberID, 2, 1), "*")
strC = Nz(Mid(strMemberID, 3, 1), "*")
strD = Nz(Mid(strMemberID, 4, 1), "*")
strE = Nz(Mid(strMemberID, 5, 1), "*")
strF = Nz(Mid(strMemberID, 6, 1), "*")
strG = Nz(Mid(strMemberID, 7, 1), "*")
strH = Nz(Mid(strMemberID, 8, 1), "*")
strI = Nz(Mid(strMemberID, 9, 1), "*")
strJ = Nz(Mid(strMemberID, 10, 1), "*")
strK = Nz(Mid(strMemberID, 11, 1), "*")
strL = Nz(Mid(strMemberID, 12, 1), "*")
strM = Nz(Mid(strMemberID, 13, 1), "*")
strN = Nz(Mid(strMemberID, 14, 1), "*")
strO = Nz(Mid(strMemberID, 15, 1), "*")
strP = Nz(Mid(strMemberID, 16, 1), "*")
strQ = Nz(Mid(strMemberID, 17, 1), "*")
strR = Nz(Mid(strMemberID, 18, 1), "*")
strS = Nz(Mid(strMemberID, 19, 1), "*")
strT = Nz(Mid(strMemberID, 20, 1), "*")
strU = Nz(Mid(strMemberID, 21, 1), "*")
strV = Nz(Mid(strMemberID, 22, 1), "*")
strW = Nz(Mid(strMemberID, 23, 1), "*")
strX = Nz(Mid(strMemberID, 24, 1), "*")
strY = Nz(Mid(strMemberID, 25, 1), "*")
strZ = Nz(Mid(strMemberID, 26, 1), "*")

strAA = Nz(Mid(strMemberID, 27, 1), "*")
strAB = Nz(Mid(strMemberID, 28, 1), "*")
strAC = Nz(Mid(strMemberID, 29, 1), "*")
strAD = Nz(Mid(strMemberID, 30, 1), "*")
strAE = Nz(Mid(strMemberID, 31, 1), "*")
strAF = Nz(Mid(strMemberID, 32, 1), "*")
strAG = Nz(Mid(strMemberID, 33, 1), "*")
strAH = Nz(Mid(strMemberID, 34, 1), "*")
strAI = Nz(Mid(strMemberID, 35, 1), "*")
strAJ = Nz(Mid(strMemberID, 36, 1), "*")
strAK = Nz(Mid(strMemberID, 37, 1), "*")
strAL = Nz(Mid(strMemberID, 38, 1), "*")
strAM = Nz(Mid(strMemberID, 39, 1), "*")
strAN = Nz(Mid(strMemberID, 40, 1), "*")
strAO = Nz(Mid(strMemberID, 41, 1), "*")
strAP = Nz(Mid(strMemberID, 42, 1), "*")
strAQ = Nz(Mid(strMemberID, 43, 1), "*")
strAR = Nz(Mid(strMemberID, 44, 1), "*")
strAS = Nz(Mid(strMemberID, 45, 1), "*")
strAT = Nz(Mid(strMemberID, 46, 1), "*")
strAU = Nz(Mid(strMemberID, 47, 1), "*")
strAV = Nz(Mid(strMemberID, 48, 1), "*")
strAW = Nz(Mid(strMemberID, 49, 1), "*")
strAX = Nz(Mid(strMemberID, 50, 1), "*")
strAY = Nz(Mid(strMemberID, 51, 1), "*")
strAZ = Nz(Mid(strMemberID, 52, 1), "*")

strBA = Nz(Mid(strMemberID, 53, 1), "*")
strBB = Nz(Mid(strMemberID, 54, 1), "*")
strBC = Nz(Mid(strMemberID, 55, 1), "*")
strBD = Nz(Mid(strMemberID, 56, 1), "*")
strBE = Nz(Mid(strMemberID, 57, 1), "*")
strBF = Nz(Mid(strMemberID, 58, 1), "*")
strBG = Nz(Mid(strMemberID, 59, 1), "*")
strBH = Nz(Mid(strMemberID, 60, 1), "*")
strBI = Nz(Mid(strMemberID, 61, 1), "*")
strBJ = Nz(Mid(strMemberID, 62, 1), "*")
strBK = Nz(Mid(strMemberID, 63, 1), "*")
strBL = Nz(Mid(strMemberID, 64, 1), "*")
strBM = Nz(Mid(strMemberID, 65, 1), "*")
strBN = Nz(Mid(strMemberID, 66, 1), "*")
strBO = Nz(Mid(strMemberID, 67, 1), "*")
strBP = Nz(Mid(strMemberID, 68, 1), "*")
strBQ = Nz(Mid(strMemberID, 69, 1), "*")
strBR = Nz(Mid(strMemberID, 70, 1), "*")
strBS = Nz(Mid(strMemberID, 71, 1), "*")
strBT = Nz(Mid(strMemberID, 72, 1), "*")
strBU = Nz(Mid(strMemberID, 73, 1), "*")
strBV = Nz(Mid(strMemberID, 74, 1), "*")
strBW = Nz(Mid(strMemberID, 75, 1), "*")

strBX = Nz(Mid(strMemberID, 76, 1), "*")
strBY = Nz(Mid(strMemberID, 77, 1), "*")
strBZ = Nz(Mid(strMemberID, 78, 1), "*")

Debug.Print "Original Member ID:  " & strA + strB + strC + strD + strE + strF + strG + strH + strI + strJ + strK + strL + strM + strN + strO + strP + strQ + strR + strS + strT + strU + strV + strW + strX + strY + strZ & _
strAA + strAB + strAC + strAD + strAE + strAF + strAG + strAH + strAI + strAJ + strAK + strAL + strAM + strAO + strAP + strAQ + strAR + strAS + strAT + strAU + strAV + strAW + strAX + strAY + strAZ & _
strBA + strBB + strBC + strBD + strBE + strBF + strBG + strBH + strBI + strBJ + strBK + strBL + strBM + strBO + strBP + strBQ + strBR + strBS + strBT + strBU + strBV + strBW + strBX + strBY + strBZ

Debug.Print "New Member ID:       " & strZ + strAX + strAZ + strY + strAV + strBV + strX + strBX + strBZ + strW + strBW + strAW + strV + strAY + strBY + strU + strBS + strAU + strT + strBT + strAT + strAQ + strBU + strAS + strR + strBR + strP + strQ + strBQ + strS + strAR + strBP + strAP + strO + strBO + strAO + strN + strBN + strAN + strM + strBM + strAM + strL + strBL + strAL + strK + strBK + strAK + strJ + strBJ + strAJ + strA + strBI + strAI + strH + strBH + strAH + strG + strBG + strAG + strBE + strBF + strAF + strE + strF + strAE + strD + strBA + strAA + strC + strBC + strAC + strB + strBB + strAB + strI + strBD + strAD

Debug.Print "Decrypted Member ID:  " & strA + strB + strC + strD + strE + strF + strG + strH + strI + strJ + strK + strL + strM + strN + strO + strP + strQ + strR + strS + strT + strU + strV + strW + strX + strY + strZ & _
strAA + strAB + strAC + strAD + strAE + strAF + strAG + strAH + strAI + strAJ + strAK + strAL + strAM + strAO + strAP + strAQ + strAR + strAS + strAT + strAU + strAV + strAW + strAX + strAY + strAZ & _
strBA + strBB + strBC + strBD + strBE + strBF + strBG + strBH + strBI + strBJ + strBK + strBL + strBM + strBO + strBP + strBQ + strBR + strBS + strBT + strBU + strBV + strBW + strBX + strBY + strBZ
'strNewMemberID = strJ + strL + strD + strA + strK + strE + strG + strC + strB + strH + strF + strI + strM + strN + strO



'NewSwap = strNewMemberID




End Function

Open in new window

Comment
Watch Question

chtullu135,

Your function code does not have an assignment statement in it. At some point you need to have a statement:

Encrypt = .....

It looks like you build a result on line 170, but then you print it and it is then forgotten.
If you want the debugging prompts, use these statements:

Debug.Print "Original Member ID:  " & strMemberID 
Debug.Print "New Member ID:       " & Encrypt

Open in new window


or better yet, take those statements out and create a new TestIt function:
Function TestIt(strMemberID as String) As String
   Debug.Print "original: " & 
   Debug.Print "new:  " & Encrypt(strMemberID)
   Debug.Print "Decrypted: " & Encrypt(Encrypt(MemberID))
End Function

Open in new window



Also, your structure:
strA = Nz(Mid(strMemberID, 1, 1), "*")

doesn't do what I think what you want.
Since you declared the input as type String, the function will fail on Encrypt(Null).
Mid returns an empty string (""), not Null,  if the starting point is beyond the end of the argument)
Perhaps you mean to use:

strA = Iif(Mid(strMemberID, 1, 1)="", "*", Mid(strMemberID,1,1))

but you could just change to
strA = Mid(strMemberID,1,1)
and it would work the same as it does currently.


Hope this helps,
pT72

Author

Commented:
Thanks for the advice. I'll try it out and get back to you as soon as I'm finished

Author

Commented:
Thank you for the assist

Explore More ContentExplore courses, solutions, and other research materials related to this topic.