How to create a CODED ID from a string?

[adrift]

At the moment I have record numbers that can be a combination of text and numerics. Ie: 1908308a

I need a fairly rudimentary way of scrambling the record number. I've tried converting to HEX, or OCT, but this won't work on record numbers with both alpha and numeric characters.

Any further ideas?

Thanks

 

[GingerR]

is this in a form? so users dont see it? how about using just a diff font, like wingdings?

 

[adrift]

this is in a form, but it is used for confidentiality purposes.. the record number is the patient record number.. and the codedid is the link that we include in the distributed database for communication and so the end user doesn't have personal identifier's.. so unfortunately, it needs to be a combination of alpha and numeric characters..

 

[Craig0201]

Adrift,

I've used the ADFGVX cipher to do this for you. My functions will ONLY take alphanumerics.

If you want a copy of the code grid, use the function ADFGVX against a table with all alphanumerics in.

Hope it helps.

Option Compare Database
Option Explicit

Function ADFGVX(strUncoded As String) As String

Dim intCount As Integer
Dim strChar As String
Dim intADFGVXPosition As Integer


For intCount = 1 To Len(strUncoded)
strChar = Mid(strUncoded, intCount, 1)
intADFGVXPosition = AscToADFGVXPosition(strChar)
Select Case Remainder(intADFGVXPosition, 6)
Case 1
ADFGVX = ADFGVX & "A"
Case 2
ADFGVX = ADFGVX & "D"
Case 3
ADFGVX = ADFGVX & "F"
Case 4
ADFGVX = ADFGVX & "G"
Case 5
ADFGVX = ADFGVX & "V"
Case Else
ADFGVX = ADFGVX & "X"
End Select
Select Case ADFGVXRow(intADFGVXPosition)
Case 0
ADFGVX = ADFGVX & "A"
Case 1
ADFGVX = ADFGVX & "D"
Case 2
ADFGVX = ADFGVX & "F"
Case 3
ADFGVX = ADFGVX & "G"
Case 4
ADFGVX = ADFGVX & "V"
Case Else
ADFGVX = ADFGVX & "X"
End Select
Next
End Function

Function AscToADFGVXPosition(strChar As String) As Integer

If Len(strChar) > 1 Then
AscToADFGVXPosition = 0
Exit Function
ElseIf Asc(strChar) > 47 And Asc(strChar) < 58 Then
AscToADFGVXPosition = Asc(strChar) - 47
Exit Function
ElseIf Asc(strChar) > 64 And Asc(strChar) < 91 Then
AscToADFGVXPosition = Asc(strChar) - 54
Exit Function
ElseIf Asc(strChar) > 96 And Asc(strChar) < 123 Then
AscToADFGVXPosition = Asc(strChar) - 86
Exit Function
Else
AscToADFGVXPosition = 0
Exit Function
End If
End Function

Function Remainder(intInput As Integer, intDivisor As Integer) As Integer

Dim intDivided As Integer

intDivided = Int(intInput / intDivisor)

Remainder = intInput - (intDivided * intDivisor)

End Function

Function ADFGVXRow(intInput As Integer) As Integer

ADFGVXRow = Int((intInput - 1) / 6)

End Function

Craig

 

[MichaelRed]

I would just use the standard XOR with some selected string. It will generally convert a real a/n string to gibberish and back to the original string with a minimum of fuss. On the ohter hand, if this is a (patient) Id, which should not be known, why is ANY representation necessary? Do 'users' somehow take the gibberish representation and MANUALLY use it to look up additional info? If so, why not just automate the additional look up and hide the Id?

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[adrift]

Thanks for the code, I'll give it a try..

The record number is available to the data abstractor but NOT to the data analysts. If a data analyst wishes to inquire about a specific case, they will refer to it through the abstractor by the coded id, not the record number.