Function Assign value to a field (1 Viewer)

JohnJan

New member
Local time
Yesterday, 23:30
Joined
Jul 11, 2011
Messages
1
I want to be able to assign value to a field based on first letter of Surname & first letter of firstname & three numbers.
i.e. Fullname = Smith, Mary Helen
Field = SM123

The following code only gets the first letter of the fullname & three numbers. Can anyone help me to fix this.

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
Dim nam2 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)
nam = Left$(nam1, 1)
Lam = Left$(Sam, 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
 
Last edited:

Users who are viewing this thread

Top Bottom