Access Cookbook - Ken Getz [125]
strName = varSurName & vbNullString
intLength = Len(strName)
strSdx = String(acbcSoundexLength, "0")
If intLength > 0 Then
intSeparator = 0 'Keeps track of vowel separators
intPrvCode = 0 'The code of the previous char
intCharCount = 0 'Counts number of input chars
intSdxCount = 0 'Counts number of output chars
'Loop until the soundex code is of acbcSoundexLength
'or we have run out of characters in the surname
Do Until (intSdxCount >= acbcSoundexLength Or intCharCount >= intLength)
intCharCount = intCharCount + 1
strChar = Mid$(strName, intCharCount, 1)
'Calculate the code for the current character
Select Case strChar
Case "B", "F", "P", "V"
intSdxCode = 1
Case "C", "G", "J", "K", "Q", "S", "X", "Z"
intSdxCode = 2
Case "D", "T"
intSdxCode = 3
Case "L"
intSdxCode = 4
Case "M", "N"
intSdxCode = 5
Case "R"
intSdxCode = 6
Case "A", "E", "I", "O", "U", "Y"
intSdxCode = -1
Case Else
intSdxCode = -2
End Select
'Special case the first character
If intCharCount = 1 Then
Mid$(strSdx, 1, 1) = UCase(strChar)
intSdxCount = intSdxCount + 1
intPrvCode = intSdxCode
intSeparator = 0
'If a significant constant and not a repeat
'without a separator then code this character
ElseIf intSdxCode > 0 And _
(intSdxCode <> intPrvCode Or intSeparator = 1) Then
Mid$(strSdx, intSdxCount + 1, 1) = intSdxCode
intSdxCount = intSdxCount + 1
intPrvCode = intSdxCode
intSeparator = 0
'If a vowel, this character is not coded,
'but it will act as a separator
ElseIf intSdxCode = -1 Then
intSeparator = 1
End If
Loop
acbSoundex = strSdx
Else
acbSoundex = Null
End If
ExitHere:
Err.Clear
Exit Function
HandleErr:
Select Case Err.Number
Case Else
MsgBox Err.Number & ": " & Err.Description, _
vbOKOnly + vbCritical, "acbSoundex"
End Select
Resume ExitHere
End Function
The acbSoundex function is based on the Russell Soundex standard algorithm. Soundex is the most commonly used sound-alike algorithm in the U.S. It works by discarding the most unreliable parts of a name, while retaining much of the name's discriminating power. It works best when used with the English versions of names of people of European descent. Its discriminating power is reduced when it is used with very short or very long names or names with a high percentage of vowels. Other sound-alike algorithms may work better in these situations.
The Soundex algorithm was created to work with people's last names. It appears to work reasonably well with people's first names also, but not for names of businesses. Soundex does not work well for business names primarily because these names tend to be longer than people's names, and Soundex encodes only the first four significant characters. We've found that extending the number of encoded characters to eight works better for business names, although this is a nonstandard implementation of the algorithm. You can easily extend the number of encoded characters by changing the acbcSoundexLength constant found at the beginning of acbSoundex. If you decide to do this, however, we suggest you rename the function to something like acbSoundex8 to distinguish it from the standard function.
Soundex will not work satisfactorily with data other than names.
6.4. Find the Median Value for a Field
Problem
You need to calculate the median for a numeric field. Access provides the DAvg function to calculate the mean value for a numeric field, but you can't find the equivalent function for calculating medians.
Solution
Access doesn't provide a built-in DMedian function, but you can make one using VBA code. This solution demonstrates a median function that you can use in your own applications.
Load the frmMedian form from 06-04.MDB. Choose the name of a table and a field in that table using the combo boxes on the form. After you choose a field, the median value will be calculated and displayed in a text box using the acbDMedian function found in basMedian (see Figure 6-7). An error message will be displayed if you have chosen a field with a nonnumeric data type;