Solved

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

Posted on 2002-04-24
23
618 Views
Last Modified: 2006-11-17
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
Comment
Question by:tonyraymond
  • 9
  • 6
  • 6
  • +2
23 Comments
 
LVL 4

Expert Comment

by:Nevaar
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

by:nico5038
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

by:tonyraymond
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

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

Nic;o)
0
 

Author Comment

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

Tony
0
 
LVL 54

Expert Comment

by:nico5038
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

by:
dovholuk earned 50 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

by:dovholuk
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

by:dovholuk
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



this should be your ticket...

dovholuk
0
 
LVL 8

Expert Comment

by:dovholuk
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

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

Nic;o)
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:tonyraymond
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

by:nico5038
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

by:tonyraymond
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

by:nico5038
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

by:dovholuk
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

by:tonyraymond
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

by:tonyraymond
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

by:dovholuk
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???

any more info?
0
 
LVL 54

Expert Comment

by:nico5038
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

by:nico5038
ID: 7170115
tonyraymond,

How far are we on this one ?

Nic;o)
0
 
LVL 54

Expert Comment

by:nico5038
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)
Please leave any comments here within the
next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

Nic;o)
0
 
LVL 5

Expert Comment

by:Netminder
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now