Need help with an array

[Tiger955]

Hi!

I Need to find the respective numbers for a textstring when for

abcdefghijkl stand the numbers
79 81 82 83 84 85 86 87 88 89 91 92

The textstring to "decode" is for example is 'adgjk'

The result (79 83 86 89 91) should be added into a table by Looping.
rs.Addnew
rs("Letter")= myarray??
rs("corNumber")= myarray?
rs.update
rs.movenext

Something like this.

But I cannot define and Setup the Array, which should be the best way for doing this.

The Array does not change its Content nor its Dimension.
Both, letters and numbers are strings.

Thanks your help
Michael

 

[CJ_London]

a string is effectively an array of characters so something like

for I=1 to len(textstring)
rs.Addnew
rs("Letter")= mid(textstring,i,1)
rs("corNumber")= asc(mid(textstring,i,1))
rs.update
rs.movenext

 

[Tiger955]

Sorry, the numbers are NOT the ASCII codes of the letters, so I was thinking of an Array.

Further more, If the letters from the string are not found in the Array, there should not be a record added.

 

[stopher]

I think a collection might help. You could use the alpha as the key to get the corresponding numeric.

Otherwise try a dictionary.

 

[stopher]

Or maybe just try the Switch function.

 

[CJ_London]

in that case you will need to declare an array. Could be one or two dimensions


for one dimension the array could be

dim Sarr() as string
sarr=split("a,b,c,d,e,f,g,h,i,j,k,l,79,81,82,83,84,85,86,87,88,89,91,92",",")

for I=1 to len(textstring)
    for j=0 to (ubound(sarr)/2)-2
        if  mid(textstring,i,1)=sarr(j)
            rs.Addnew
            rs("Letter")= mid(textstring,i,1)
            rs("corNumber")= sarr(j+12)
            rs.update
            rs.movenext 
        end if
    next j
next i

 

[Tiger955]

Thanks a lot CJ, thats exactly what I was looking for!

 

[stopher]

If the letters are all lowercase, then you could simplify:

For i = 1 To Len(textstring)
    rs.AddNew
    rs("Letter") = Mid(textstring, i, 1)
    rs("corNumber") = Choose(Asc(Mid(textstring, i, 1)) - 96, 79, 81, 82, 83, 84, 85, 86, 87, 88, 89, 91, 92)
    rs.Update
Next i

 

[Tiger955]

@stopher
Thanks yr. reply, but as I said, it cannot be solved with ASC as the numbers are not in any correlation to the letters. There are just numbers in a sequence of 12 without Zeros that they cannot be mixed up with letter o.

 

[stopher]

@stopher
Thanks yr. reply, but as I said, it cannot be solved with ASC as the numbers are not in any correlation to the letters. There are just numbers in a sequence of 12 without Zeros that they cannot be mixed up with letter o.

Did you test the code? I've used Asc to generate an index to use a a lookup. The Choose function uses that index to look up a value to return. The values returned by Choose can be anything you wish to define.

 

[Tiger955]

No stopher, not yet, but I will do it tomorrow in the Office and Report. Thanks yr. efforts.

 

[Tiger955]

Hi stopher!

This works even smarter!!

I still do not understand choose (although I found it on the net - never used choose before!) and why to have 13 values for 12 letters, but it works.

I had to add an if-clause to avoid letters larger than "l"

For i = 1 To Len(textstring)
'rs.AddNew
strletter = Asc(Mid(textstring, i, 1))
'Debug.Print strletter
If strletter > 96 And strletter < 109 Then
corNumber = Choose(Asc(Mid(textstring, i, 1)) - 96, 79, 81, 82, 83, 84, 85, 86, 87, 88, 89, 91, 92)
Debug.Print strletter; vbTab; corNumber
End If
'rs.Update

Next i

Thanks a lot!

 

[Tiger955]

Aah, now I got it!!

Choose.. and until the first comma, its the calculation of the position!!!
The values behind are the indexed values...!

 

[CJ_London]

be aware the choose function 'chooses' based on a value from 1 to whatever. So it will work providing you have a,b,c.... but will fail if you have a,c,d

 

[stopher]

be aware the choose function 'chooses' based on a value from 1 to whatever. So it will work providing you have a,b,c.... but will fail if you have a,c,d

Indeed, it is a bit rigid and dealing with a specific case. It will also not recognised "A", "B" etc which may not be a good thing. I gave it more as a "for added interest" thing as a good solution had already been suppied by you.

Actually I would prefer something like this:

Dim aCol As New Collection
aCol.Add 79, "a"
aCol.Add 81, "b"
aCol.Add 82, "c"
aCol.Add 83, "d"
aCol.Add 84, "e"
aCol.Add 85, "f"
aCol.Add 86, "g"
aCol.Add 87, "h"
aCol.Add 88, "i"
aCol.Add 89, "j"
aCol.Add 91, "k"
aCol.Add 92, "l"


For i = 1 To Len(textstring)
    rs.AddNew
    rs("Letter") = Mid(textstring, i, 1)
    rs("corNumber") = aCol(Mid(textstring, i, 1))
    rs.Update
Next i

The problem with the array approach is you have to search the array (in your example the whole array) for each lookup. But my instincts tell me this is inefficient and we should really use a purpose built lookup method which should be optimised for lookup.

So the example I give above creates a collection where the required value (number) can be looked up using the index (letter). If this code is going to be run many times e.g. on a whole dataset, then aCol could be set up prior to running the code e.g. as a global.

Another method of lookup could be to create a table to store the lookup indexes and values and then just use DLookup. Might be slower though.

The Switch function is also an option.

Select Case is also a option.

I guess the choice comes down to what the function is goint to be used for. I suspect your original solution will be fine for anything but severly large datasets with long words to decode.

 

[CJ_London]

all useful alternatives, as you say, depends on the user requirements.

Advantage of the array is it's flexibility - the text string can be built or even just typed in with no changes to the code. It may be slower than other options, but I don't have the time to experiment

Interesting idea re using a collection - what happens if Mid(textstring, i, 1) is not in the collection? returns null or errors?

 

[Tiger955]

I could not find any Problems when Decoding "adhl" for example by Choose, the results where correct.

As adhl is different to ADHL or any mixute of it, I convert the string By LCase("AdHl") before Splitting it.

I will try Collection too, which I never used before.

Thanks both of you!

 

[Tiger955]

Just for info:

collection works best, but you have to use "On error resume next".
Otherwise you fail by error if another letter will be sumitted.