String Functions Needed please help

[Chance1234]

Hi i need couple of string functions if anyone has them to hand,

firstly a function to count different letters in a string

a funciton to rearrange all the letters in a string into alphabetical order

Much appreciated


Chance

http://www.5ylac.s5.com

 

[jebry]

Hi Chance!

Just curious, what are you doing with these functions? They aren't particularly difficult, but they aren't very useful either.

Jeff Bridgham
bridgham@purdue.edu

 

[Chance1234]

got some serious messed up data which im trying to match grabbing as many string functions as i can to try and get a best match result

 

[jebry]

Hi again!

Post some examples of the data and what you are trying to match and we can probably come up with some better functions that are more useful then the ones you asked for. My e-mail is at the bottom of this post if you would rather send the data that way than post it.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[Chance1234]

Hi

OK here is my problem, i have two tables, one table is a list of names and a reference.(402) records. the next table i have is a trawl of a trading system and that contains(70'000) records. what i need to do is for each of the 402 records find a "best match" in the trading table.

now this trading system table has had infomation pulled from several other sub systems etc etc.

now heres the problem the only thing common between the two tables is a name field! and heres the glith

in my reference table i might have the name

Joe, Bloggs

in the trading system table it could eb in there as

J. Bloggs
Bloggs, Joe
Joe Bloggs
Joe P.Bloggs
Joe.Bloggs
Bloggs.Joe

etc etc , waht iwas thinking of doign was rearranging all the string into alpghabvetic order striping any fuill stops etc and doing a word score match on the letters

any help greatully appreciated


Chance

http://www.5ylac.s5.com

 

[jebry]

Hi Chance!

It seems to me that what you want to do is parse the name field from the reference table and do a search base on that. One question though, are the names put in the reference table in a standard manner? Is Joe, Bloggs consistant or is it as bad as the other table?

Let me know as soon as possible and we will get you the type of function you are needing
Jeff Bridgham
bridgham@purdue.edu

 

[Klopper]

Hi there, the code below returns the Soundex value for a string.
For example: both "J. Bloggs" and "Joe Bloggs" have a Soundex value J14200, (but "Bloggs, J" gives "B42000" so will need to be handled before passing into the Soundex routine).

Function ahtSoundex(ByVal varSurName As Variant) As Variant
' Purpose:
' Takes a surname string and returns a 4-digit
' code representing the Russell Soundex code.
'
' From Microsoft Access 95 How-To
' by Getz and Litwin. (Waite Group)
' Copyright 1995. All Rights Reserved.
'
' In:
' varSurName: A surname (last name) as a variant
' Out:
' Return value: A 4-digit Soundex code as a variant
On Error GoTo ahtSoundexErr

Dim intLength As Integer
Dim intCharCount As Integer
Dim intSdxCount As Integer
Dim intSeparator As Integer
Dim intSdxCode As Integer
Dim intPrvCode As Integer
Dim varChar As Variant
Dim varSdx As Variant

Const ahtcSoundexLength = 6

' We add "" to take care of a passed Null
intLength = Len(varSurName & "&quot

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 ahtcSoundexLength
'or we have run out of characters in the surname
Do Until (intSdxCount = ahtcSoundexLength Or intCharCount = intLength)
intCharCount = intCharCount + 1
varChar = Mid(varSurName, intCharCount, 1)

'Calculate the code for the current character
Select Case varChar
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
varSdx = UCase(varChar)
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
varSdx = varSdx & 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

'If the code is < ahtcSoundexLength chars long, then
'fill the rest of code with zeros
If intSdxCount < ahtcSoundexLength Then
varSdx = varSdx & String((ahtcSoundexLength - intSdxCount), &quot;0&quot
End If

ahtSoundex = varSdx
Else
ahtSoundex = Null
End If

ahtSoundexDone:
On Error GoTo 0
Exit Function

ahtSoundexErr:
Select Case err
Case Else
MsgBox &quot;Error#&quot; & err & &quot;: &quot; & Error$, _
vbOKOnly + vbCritical, &quot;ahtSoundex&quot;
End Select
Resume ahtSoundexDone

End Function

 

[Sawatzky]

dear jebry,

asuming in yourreference table in the followinf as ref_table you have allways (name, lastname)
trad_table = trading table
you could do :


SELECT ref_table.name,trad_table.name
FROM ref_table,trad_table
WHERE (((trad_table.name) Like &quot;*&quot; & Right$([ref_table]![name],Len([[ref_table]![name])-InStr(1,[[ref_table]![name],&quot;,&quot))) or (((trad_table.name) Like Right$([ref_table]![name],Len([[ref_table]![name])-InStr(1,[[ref_table]![name],&quot;,&quot) & &quot;*&quot);

hope I did not add to many or to few brackets


regards astrid