Using an array for Lookup

T

Tiger955

Registered User.
Local time
Today, 07:27
Joined
Sep 13, 2013
Messages
140
Hi!
I have to decode a string into numbers and to avoid to find out the values for 47 options by select case I though about an array.

Ich want to decode
Number Letter
10 A
11 B
12 C
13 D
14 E
15 F
16 G
17 H
18 I
19 J
20 K
...

For example the string "ADEG" would give as result
10 13 14 16

So I would have to loop through the string and "decode" each letter into a number.

As I have still problems to understand array, pls help me also in defining the dimmension of the array, it has fix 47 entries to decode

Dim myarray (47,2) as variant
mayarray=(10,"A",11,"B",...)

Correct?

Thanks your help
Michael
 
Would you not be better off with tables? Store them in Tables and lookup the values? Even if you are using Arrays why 47? There are only 26 letters?
 
I agree with Paul -- suggest you use a table. And why 47?

Can you tell us in plain English, the reason for the decode?
 
47: digits 1 to 9, letters A to Z, the rest are signs like %-/&$, total 47.

Tbls are possible, but not prefered as the code should be protected and will never change.

It is about decoding numeric strings like "10348977" into Hex "03EB11A11" including a checkdigit.

Therefore I need to place an array (and I think this should solve it best) within the code and when you enter the number 10348977 in a field of a form a barcode label "03EB11A11" will be generated.

All I need is a solution that finds me the value 10 when entered an "A", the value 19 when entered a "J" and the value 39 when entered a "%" sign (up to my entries).

Of course there is an intermediate step from 10348977 to some parts of "03EB11A11", because some digits in the result I can calculate, the checksum requires my request for help.
 
You could write a function along the lines of ...

Code: 
Public function str2Num(byval theString as String)
Dim lngIndex as Long, strChar as String, strTemp as String

  strTemp = vbNullString

  For lngIndex = 1 to Len(theString)
   strChar = lcase(Mid(theString, lngIndex,1))

    Select Case strChar
    Case "a"
      strTemp = strTemp & "10"

    Case "j"
      strTemp = strTemp & "19"

    Case "%"
      strTemp = strTemp & "39"

  Next lngIndex

  str2Num = strTemp
End Function

That's just an example and would need more lines to cope with the rest of the characters.
 
Last edited:
47: digits 1 to 9, letters A to Z, the rest are signs like %-/&$, total 47.
Click to expand...

What about 0 (zero)?
 
It could also be done using a lookup string.

Code: 
Public Function str2Num(ByVal theString As String)
Dim lngIndex As Long, strChar As String, lngChar As Long, strTemp As String
Dim strCharacters As String

  strCharacters = "123456789" _
  & "abcdefghijklmnopqrstuvwxyz..."

  strTemp = vbNullString

  For lngIndex = 1 To Len(theString)
   strChar = LCase(Mid(theString, lngIndex, 1))

   lngChar = InStr(strCharacters, strChar)
   strTemp = strTemp & Format(lngChar, "00")

  Next lngIndex

  str2Num = strTemp
End Function

I attach an example of its use.

tblBeforeStrings - A table of various strings
qryEncode - An example of the results
str2Num() - a function in Module1 to encode the strings

The function loops through the source string and finds the location of each character in the string strCharacters and transforms its location into a 2 character numeric string.


Code: 
Before		After
1		01
2		02
a		10
ab12		10110102
asdfghjk	1028131516171920
b		11
 

Attachments

Last edited:
nanscombe said:
You could write a function along the lines of ...

Code: 
Public function str2Num(byval theString as String)
Dim lngIndex as Long, strChar as String, strTemp as String
 
  strTemp = vbNullString
 
  For lngIndex = 1 to Len(theString)
   strChar = lcase(Mid(theString, lngIndex,1))
 
    Select Case strChar
    Case "a"
      strTemp = strTemp & "10"
 
    Case "j"
      strTemp = strTemp & "19"
 
    Case "%"
      strTemp = strTemp & "39"
 
  Next lngIndex
 
  str2Num = strTemp
End Function

That's just an example and would need more lines to cope with the rest of the characters.
Click to expand...
Thanks Nigel, that's what I wanted to avoid cause it needs 47 cases. It works but it is not elegant.;)

Yesterday evening I found a solution which I can live with (exept something can help me with an array solution as I still thinak this can be solved with an 2-dim array as well, but do not know how!) with could work like this.

Function TestString()
Dim strSTring As String
strSTring = ")" 'for example
Select Case strSTring
Case "A" To "Z"
Debug.Print Asc(strSTring) - 55
Case "0" To "9"
Debug.Print CInt(strSTring)
Case "&", "%", "/"
Debug.Print Asc(strSTring) - 9
Case ")"
Debug.Print Asc(strSTring) - 7
End Select

It's a select case too, but the letters and digits can be built into groups this works and i have only to loop through the whole string ( e.g. "03XY6104" to get my result.

Thanks your help!
 
Here is the array attempt if you are still looking for one !
Code: 
Public Function decodeString2(inputStr As String) As String
[COLOR=Green]'**********************
'Code Courtesy of
'  Paul Eugin
'**********************[/COLOR]
    Dim tmpArr
    tmpArr = Array("1", "2", "3", "4", "5", "6", "7", "8", "9", "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", _
                   "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", "%", "-", "/", "&", "$", "+", "*", "£", "!", ".", "{", "}")
    
    Dim fVar As String, iCtr As Long
    Dim lCtr As Long, aCtr As Long
    Dim sStr As String
    
    lCtr = Len(inputStr)
    For iCtr = 1 To lCtr
        sStr = Mid(inputStr, iCtr, 1)
        For aCtr = 0 To 46
            If sStr = tmpArr(aCtr) Then Exit For
        Next
        fVar = fVar & " " & aCtr + 1
    Next
    decodeString2 = Trim(fVar)
End Function
 
Attached is another version which also contains code based on your solution above. I've added spaces between the characters for clarity.

Code: 
Before	AfterMine	AfterYours
%	36		28
%S	36 28		28 28
&	37		29
&T	37 29		29 29
)	38		34
/	39		38
0	00		0
1	01		1
2	02		2
a	10		10
AB012	10 11 00 01 02	10 11 0 1 2
ASDFG	10 28 13 15 16	10 28 13 15 16
B	11		11

There are a couple of problems with your solution:
1) For numbers 0 to 9 your code will only give you a single character (easily fixed)
2) The '%' and '&' will produce the same result as 'S' and 'T' respectively.

As usual take a look and see what you think.

My function.

Code: 
Public Function Test01(ByVal theString As String)
Dim lngIndex As Long, strChar As String, lngChar As Long, strTemp As String
Dim strCharacters As String

' ======================================================================
  ' The order of the string is critical to the output.
  ' If characters are added before the end then the output will change
  
  strCharacters = "0123456789" _
  & "ABCDEFGHIJKLMNOPQRSTUVWXYZ" _
  & "%&)/"
' ======================================================================

  strTemp = vbNullString

  For lngIndex = 1 To Len(theString)
    strChar = UCase(Mid(theString, lngIndex, 1))

    lngChar = InStr(strCharacters, strChar)

    strTemp = strTemp & " " ' temporary line to add a space between characters

    strTemp = strTemp & Format(lngChar - 1, "00")

  Next lngIndex

Test01 = Trim(strTemp)
End Function
 

Attachments

Last edited:
Nigel, Paul!

Thanks y lot for your efforts, I will take the best of all solutions!!

Michael
 
You must log in or register to reply here.

Similar threads

N
Add RowNumber Problem
Replies
11
Views
307
CJ_London
C
H
Solved Finding names from table of not duplicated rows
Replies
11
Views
224
Pat Hartman
P
G
Code compilation problem
Replies
9
Views
791
George-Bowyer
G
K
Group tables
Replies
14
Views
467
jdelano
J
S
Anyone can help on this ? Total Date In Access
Replies
2
Views
287
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom