assign value to a field

[JohnJoker]

Hi Guys

I have got the following function, that Generates value to a field by getting the first letter of Firstname, Surname + 3 numbers.
I am having an error "Subscript out of range" on the following lineline


"ip = k(Asc(Mid$(nam, 1, 1)))"



Function Soundex(na As String) As String
Dim m1 As Variant
Dim m2 As Variant
Dim k(65 To 90) As Integer
Dim i, nx, np, n, k1 As Integer
Dim nam As String
Dim nam1 As String

m1 = Array("A", "E", "I", "O", "U", "Y", "W", "H", "B", "F", "P", "V", "C", "G", "J", "K", "Q", "S", "X", "Z", "D", "T", "L", "M", "N", "R")
m2 = Array(0, 0, 0, 0, 0, 0, 0, 0, 1, 1, 1, 1, 2, 2, 2, 2, 2, 2, 2, 2, 3, 3, 4, 5, 5, 6)

For i = 0 To 25
k(Asc(m1(i))) = m2(i)
Next

nam1 = UCase(na)
' remove non-letter characters:
nam = Left$(nam1, 1)
For i = 2 To Len(nam1)
n = Asc(Mid$(nam1, i, 1))
If n >= 65 And n <= 90 Then
nam = nam & Mid$(nam1, i, 1)
End If
Next
' generate the soundex code:
Soundex = Mid$(nam, 1, 1) & "000"
nx = 1
ip = k(Asc(Mid$(nam, 1, 1)))
For n = 2 To Len(nam)
i = k(Asc(Mid$(nam, n, 1)))
If i <> 0 And i <> ip Then
nx = nx + 1
If nx < 4 Then
Soundex = Left$(Soundex, nx - 1) & i & Right$(Soundex, 4 - nx)
Else
Soundex = Left$(Soundex, nx - 1) & i
End If
End If
If Mid$(nam, n, 1) <> "H" And Mid$(nam, n, 1) <> "W" Then
ip = i
End If
If nx = 4 Then
Exit For
End If
Next
End Function

How to fix it?

Thanks,

 

[PHV]

Replace this:
' remove non-letter characters:
nam = Left$(nam1, 1)
For i = 2 To Len(nam1)
n = Asc(Mid$(nam1, i, 1))
If n >= 65 And n <= 90 Then
nam = nam & Mid$(nam1, i, 1)
End If
Next
with this:
' remove non-letter characters:
nam = ""
For i = 1 To Len(nam1)
n = Asc(Mid$(nam1, i, 1))
If n >= 65 And n <= 90 Then
nam = nam & Mid$(nam1, i, 1)
End If
Next

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JohnJoker]

Hi

The error that is comming up now "is invalid procedure call or argument" same line

ip = k(Asc(Mid$(nam, 1, 1)))

Thanks,

 

[PHV]

Well, test Len(nam) ....

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JohnJoker]

Sorry, has solved. The problem is that it is generating the field but for same reason does like to read the first letter of the Firstname (i.e Name=Cyrus,Andrew) it is geerating (i.e field=C264)

Thanks,

 

[JohnJoker]

Sorry, it has not solved. The problem is that it is generating the field but for same reason does like to read the first letter of the Firstname (i.e Name=Cyrus,Andrew) it is geerating (i.e field=C264)

Thanks,

 

[SkipVought]

What is variable k???

you essentially have

ip = k([i]some number[/i])

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[JohnJoker]

K is:

m1 = Array("A", "E", "I", "O", "U", "Y", "W", "H", "B", "F", "P", "V", "C", "G", "J", "K", "Q", "S", "X", "Z", "D", "T", "L", "M", "N", "R")
m2 = Array(0, 0, 0, 0, 0, 0, 0, 0, 1, 1, 1, 1, 2, 2, 2, 2, 2, 2, 2, 2, 3, 3, 4, 5, 5, 6)

For i = 0 To 25
k(Asc(m1(i))) = m2(i)
Next