String manipulation - convert strings to numbers

I am still rather new at visual basic programming (being an Access2000 developer) and I cannot come up with a solution to the following problem :

I need to create a new string 5 digits in length from a another string of any length, and convert it to the new 5 digit string
based on the following conditions : The first digit of the new string is the first letter of the old string, and the last digit of the new string is a zero. The second, third and fourth strings are based on the following formula, if those letters are the following, then they are assigned the equivalent number:

A-C=1, D-F=2, G-I=3, J-L=4, M-O=5, P-R=6, S-U=7, V-X=8,Y-Z=9

So, given the old string "Manpower", the new string would be "M1560"

The following function is what I came up with, and, it does not work. I am still so new at this I just can't put all the pieces of string manipulation functions together yet, and I do so hope that one day I will be able to easily come up with the solution to a problem like this one ! I appreciate your help ! Please broaden my knowledge with your advice ! I thank you very much !

Terry Caldwell
[email protected]

Function GetAccountSuffix(Name) As Variant

Dim suffix As Variant, suffix1 As Variant, suffix5 As Variant, 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 As Variant
suffix1 = Mid(Name, 1, 5)
suffix5 = 0
suffix = Mid(Name, 2, 4)

Select Case suffix
Case A Or B Or C
suffix = 1
Case D Or E Or F
suffix = 2
Case G Or H Or I
suffix = 3
Case J Or K Or L
suffix = 4
Case M Or N Or O
suffix = 5
Case P Or Q Or R
suffix = 6
Case S Or T Or U
suffix = 7
Case W Or X Or Y
suffix = 8
Case Else
suffix = 9

Name = suffix1 & suffix & suffix5
End Select
End Function

Probably the best way to do this is with a switch statement:

Function NewFromOld(byval sOld, sNew) as Boolean
Dim i as Integer
Dim sChr as string

If len(sOld) <= 5 then
NewFromOld = false
else
sNew = UCase(Left$(sOld, 1))
for i = 2 to 4
sChr = UCase(mid$(sOld, i, 1))
sNew = sNew & CStr(Switch(sChr Like "[A-C]", 1, sChr Like "[D-F]", 2, _
sChr Like "[G-H]", 3, sChr Like "[I-K]", 4, _
sChr Like "[J-L]", 4, sChr Like "[M-O]", 5, _
sChr Like "[P-R]", 6, sChr Like "[S-U]", 7, _
sChr Like "[V-X]", 8, sChr Like "[YZ]", 9))
next 'i
sNew = sNew & "0"
NewFromOld = true
End If
End Function




This should give you precisely the answer you are looking for.