Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

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

Posted on 2002-04-24
Medium Priority
648 Views
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!!!
0
Question by:tonyraymond
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 9
• 6
• 6
• +2

LVL 4

Expert Comment

ID: 6966755
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

LVL 54

Expert Comment

ID: 6966822
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 Comment

ID: 6966824
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

LVL 54

Expert Comment

ID: 6966853
So my proposal would "fit".....

Nic;o)
0

Author Comment

ID: 6966858
Nico,
Yep, working on it now.

Tony
0

LVL 54

Expert Comment

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

Nic;o)
0

LVL 8

Accepted Solution

dovholuk earned 200 total points
ID: 6966864
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

LVL 8

Expert Comment

ID: 6966875
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

LVL 8

Expert Comment

ID: 6966888
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

LVL 8

Expert Comment

ID: 6966893
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

LVL 54

Expert Comment

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

Nic;o)
0

Author Comment

ID: 6966926
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

LVL 54

Expert Comment

ID: 6966948
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 Comment

ID: 6967039
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

LVL 54

Expert Comment

ID: 6967210
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

LVL 8

Expert Comment

ID: 6967257
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 Comment

ID: 6967276
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 Comment

ID: 6967285
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

LVL 8

Expert Comment

ID: 6967286
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

LVL 54

Expert Comment

ID: 6967684
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

LVL 54

Expert Comment

ID: 7170115
tonyraymond,

How far are we on this one ?

Nic;o)
0

LVL 54

Expert Comment

ID: 7183033

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

LVL 5

Expert Comment

ID: 7199465
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

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are â€¦
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance andâ€¦
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which â€¦
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient â€¦
###### Suggested Courses
Course of the Month9 days, 4 hours left to enroll