Creating A Code

[EmmaJane]

Can anyone help?

Hi

I was hoping someone could help with this. I have a field that appears on a report. In this field I have a number code that is between 5 and 8 numbers. This code is highly confidential but must appear in some form in this report. I was hoping that if each number were assigned a letter there would be a way of having the letters appear automatically on the report.

Thank You in advance

 

[GumbyD]

EmmaJane -

It sounds to me like you have more than one issue going on here. It would not be very difficult to create some code that replaced numbers with a letter equilivilant from a code table. I guess my concern is that if you use a simple code to convert the numbers so that the people who see them on the report can decrypt the data and get the real number than it will be easy for someone who is not supposed to see the numbers to decrypt it as well, but if you make the encryption to complex then the value on the report will be meaningless.

In any case if you want to convert a number to a letter you can create a code table that holds the conversions - something like:
Number Letter
1 C
2 Z
3 X
Etc.

Then on the query behind the report you will need to call a custom function you create that reads the number string and pulls character by character in a loop a creates the new string to display on the report. If you need assistance with that code let me know and I will post something for you to use.

GumbyD

 

[EmmaJane]

GumbyD,

Thanks for the advice. I know that this solution may not be the safest but at the moment it’s the only way I can see to solve the issues I have.

I have created a table as you suggest but any help you can offer on the query etc would be appreciated.

 

[GumbyD]

EmmaJane -

Here is some code that should work for you. Please see the attached image to see how I used the code in a query.

Public Function ConvertNumber(IDNum As Long) As String
Dim rstConv As Recordset
Dim X As Integer
X = 0
Set rstConv = CurrentDb.OpenRecordset("numberconverter", dbOpenSnapshot)
For X = 1 To Len(CStr(IDNum))
rstConv.FindFirst ("number = " & Right(Left(IDNum, X), 1))
ConvertNumber = ConvertNumber & rstConv!Letter
Next X
rstConv.Close

End Function

GumbyD