# function to convert 13 digit numeric field to 7 or fewer alphanumeric

I have a table of 13 digit numbers, all unique.  I have a requirement to fill a pre-existing field that can only hold 7 characters.  Is anyone aware of an algorithm that I might use get the 13 digit number to hash down to 7 alphanum?

Thank you experts!!!
###### Who is Participating?

x

Commented:
so i tried hex at first too... instead, i came up with a "base 100" approach.

basically i broke it down into this. try this method with as many 13 digit numbers as you have.

Function testmyjobk()

Dim a As String
Dim b As String

a = "1234567890123"

b = Convert13DigitNumber(a)

MsgBox UnConvert13DigitNumber(b)

End Function

Function Convert13DigitNumber(num As String) As String
Convert13DigitNumber = Left(num, 1) _
& Chr(Mid(num, 2, 2)) _
& Chr(Mid(num, 4, 2)) _
& Chr(Mid(num, 6, 2)) _
& Chr(Mid(num, 8, 2)) _
& Chr(Mid(num, 10, 2)) _
& Chr(Mid(num, 12, 2))
End Function

Function UnConvert13DigitNumber(num As String) As String
UnConvert13DigitNumber = Left(num, 1) _
& Asc(Mid(num, 2, 1)) _
& Asc(Mid(num, 3, 1)) _
& Asc(Mid(num, 4, 1)) _
& Asc(Mid(num, 5, 1)) _
& Asc(Mid(num, 6, 1)) _
& Asc(Mid(num, 7, 1))
End Function

i can comment on "how" it works if you like...

dovholuk

0

Commented:
If you use capital letters and numbers, with seven characters you can only get 2.5 billion unique numbers (which isn't even a full 10 digits)

Does your 13 digit table contain over 2.5 billion records?  Or is the field just that big?  Does it need to be an algorithm or can this be a start at A000000 and start incrementing up to ZZZZZZZ one-time load?
0

Commented:
You can "code" the field hexadecimal and seven digits are then sufficient to store 14 digits.
It won't be possible to print the code without "unpacking" it.
The pseudocode to be used for "packing" is:
CHR(digit 1 * 16 + digit 2) = newdigit 1
CHR(digit 3 * 16 + digit 4) = newdigit 2
etc.
Unpacking is reverse:
ASCII([newdigit 1]) / 16 = digit 1 (in integer format)
ASCII([newdigit 1]) MOD 16 = digit 2

Possible ?

Nic;o)
0

Author Commented:
It needs to be an algorithm, I have around 80,000 of the 13 digit numbers, I want to be able to feed the 13 digit number, retrieve a 7 alphanumum representation, and use in reverse to get the 13 digit number back.  It isn't a one time load, I will need to add more later.

Thanks,

Tony
0

Commented:
So my proposal would "fit".....

Nic;o)
0

Author Commented:
Nico,
Yep, working on it now.

Tony
0

Commented:
Keep in mind it's just "air-code", let me know when you run into trouble ;-)

Nic;o)
0

Commented:
well... it fails when there are too many 0's in a row... i'm trying to work that out... ;)

be back in a bit...

dovholuk
0

Commented:
got it... the problem was with the string "00" equaling "0"... to correct it, i had to use the RIGHT of 100 + x (returning 01, 00, 05, etc)

Option Compare Database
Option Explicit

Function testmyjobk()

Dim a As String
Dim b As String

a = "2000000000002"
b = Convert13DigitNumber(a)

MsgBox UnConvert13DigitNumber(b) = a

End Function

Function Convert13DigitNumber(num As String) As String
Convert13DigitNumber = Left(num, 1) _
& Chr(Mid(num, 2, 2) + 40) _
& Chr(Mid(num, 4, 2) + 40) _
& Chr(Mid(num, 6, 2) + 40) _
& Chr(Mid(num, 8, 2) + 40) _
& Chr(Mid(num, 10, 2) + 40) _
& Chr(Mid(num, 12, 2) + 40)
End Function

Function UnConvert13DigitNumber(num As String) As String
UnConvert13DigitNumber = Left(num, 1) _
& Right(100 + Asc(Mid(num, 2, 1)) - 40, 2) _
& Right(100 + Asc(Mid(num, 3, 1)) - 40, 2) _
& Right(100 + Asc(Mid(num, 4, 1)) - 40, 2) _
& Right(100 + Asc(Mid(num, 5, 1)) - 40, 2) _
& Right(100 + Asc(Mid(num, 6, 1)) - 40, 2) _
& Right(100 + Asc(Mid(num, 7, 1)) - 40, 2)
End Function

dovholuk
0

Commented:
by the way, i arbitrarily used 40 as an offset. the original code would use chr(0) and i found that was doing odd things to the resulting strings. also, i used 40 because chr(40) is a "visible" character, vs. spaces, tabs etc. so 40 was a good offset.

enjoy.

dovholuk
0

Commented:
hmm, dovholuk triggered another idea, unpacking the "hex-coded" field can use:
right("00" & hex([newdigit 1]),2)

Nic;o)
0

Author Commented:
Nico,
This gives me a funny looking output, is this desired?

Public Function SwitchIt()
Dim sample As String
Dim digit1 As Integer
Dim digit2 As Integer
Dim digit3 As Integer
Dim digit4 As Integer
Dim digit5 As Integer
Dim digit6 As Integer
Dim digit7 As Integer
Dim digit8 As Integer
Dim digit9 As Integer
Dim digit10 As Integer
Dim digit11 As Integer
Dim digit12 As Integer
Dim digit13 As Integer
Dim newdigit1 As String
Dim newdigit2 As String
Dim newdigit3 As String
Dim newdigit4 As String
Dim newdigit5 As String
Dim newdigit6 As String
Dim newdigit7 As String
Dim output As String

sample = InputBox("Enter SampleNumber", "Enter Value Please", 1234567890123#)

digit1 = Left(sample, 1)
digit2 = Mid(sample, 2, 1)
digit3 = Mid(sample, 3, 1)
digit4 = Mid(sample, 4, 1)
digit5 = Mid(sample, 5, 1)
digit6 = Mid(sample, 6, 1)
digit7 = Mid(sample, 7, 1)
digit8 = Mid(sample, 8, 1)
digit9 = Mid(sample, 9, 1)
digit10 = Mid(sample, 10, 1)
digit11 = Mid(sample, 11, 1)
digit12 = Mid(sample, 12, 1)
digit13 = Right(sample, 1)

newdigit1 = Chr(digit1 * 16 + digit2)
newdigit2 = Chr(digit3 * 16 + digit4)
newdigit3 = Chr(digit5 * 16 + digit6)
newdigit4 = Chr(digit7 * 16 + digit8)
newdigit5 = Chr(digit9 * 16 + digit10)
newdigit6 = Chr(digit11 * 16 + digit12)
newdigit7 = Chr(digit13 * 16)

output = newdigit1 & newdigit2 & newdigit3 & newdigit4 & newdigit5 & newdigit6 & newdigit7
MsgBox "output is " & output, vbOKOnly

End Function
0

Commented:
That's correct, I "warned" that the code isn't readable.
When no zero's are there you can "read" the coded field back in 13 digits with:
output = Hex(Asc(newdigit1)) & Hex(Asc(newdigit2)) & Hex(Asc(newdigit3)) & Hex(Asc(newdigit4)) & Hex(Asc(newdigit5)) & Hex(Asc(newdigit6)) & Hex(Asc(newdigit7))

Nic;o)
0

Author Commented:
What I need to do is store that value in a text field.  I might be taking the really long way around the block on this, perhaps it would be better to let an autonumber field be created, and just keep a translation table of Autonumber to actual number??

Dholvul
0

Commented:
An extra table is ofcourse possible, but will ask JOIN's to query.
With a CodeTo and CodeFrom function once written you save that...
BTW why the 7 digit constraint?

Nic;o)
0

Commented:
did you give my solution a try? it converts the 13 digit number into a "compressed" version (much like nico's) but it's a full encode/decode combination...

just run a query such as:

SELECT Your13DigitNumberField, Convert13DigitNumber(Your13DigitNumberField)
FROM YourTable

this will return both the original 13 digit number, AND the converted one. you then run an update on the 7 character field and you're done...

anyway... good luck

dovholuk
0

Author Commented:
What I need to do is store that value in a text field.  I might be taking the really long way around the block on this, perhaps it would be better to let an autonumber field be created, and just keep a translation table of Autonumber to actual number??

Dholvul
0

Author Commented:
sorry for the repost of the previous, my browser is whacked today.

Nico,
The 7 digit constraint is due to  production system that was written 40 years ago...  It is funny really, the field can hold 30 characters everywhere except the master that it always refers to...

I don't know if I can use nonletter/numbers or not.  I am going to test now, but I don't think so, so far I can't even use lowercase letters...

Dovholuk,
Thank you also.  This has been a fun one.
0

Commented:
when you say "store the value in a text field", what do you mean and why?

do you mean on a form, you need to store it on a form? or in a table?

does it need to be "displayed" to the users???

0

Commented:
If it's on a mainframe, then the "hex-code" I gave you should be changed into:
1234567890123c
By placing in the last digit a hex C (decimal 12), you get a so-called "packed" digit representation you can read with on the mainframe. The "c" value tells it's a neutral (not negative) value. (In COBOL use a Comp-3 field ;-)

My idea was effectively based on that old field format.

Nic;o)
0

Commented:
tonyraymond,

How far are we on this one ?

Nic;o)
0

Commented:

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in Community Support that this question is:
- Answered by: nico5038 (50pts) dovholuk (50pts)
next seven days.

Nic;o)
0

Commented:
Per recommendation, force-accepted.

Netminder
CS Moderator

nico5038: points for you at http://www.experts-exchange.com/jsp/qShow.jsp?ta=msaccess&qid=20332626
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.