Comparing lists of similar text strings

[Ste4en]

I have a monthly safety slogan competition which requires back checking to a list of already submitted slogans. This takes forerver to do. I have 2 lists: this month's slogans and a master list of all slogans.

Here is an example:
S1Master: An unsafe behavior can bring you down
S1Submitted: Unsafe behaviors can bring you down

I am thinking that after removing the plural "s", and then counting the number of words that are in both sentences, as a percentage of the total number of words. Percentage greater than 90% say would be listed as a match.

6 words in 2 sentences the same = 12/13 words = 92.31%
therefore this is a matched pair.

Is this the best way, and then how do I go about comparing. Please guide me.

Thanks
Steve

 

[Kodo]

Pattern matching is a perfect thing for regular expressions.
http://www.4guysfromrolla.com/webtech/090199-1.shtml
4GuysFromRolla.com - An Introduction to Regular Expression with VBScript

Disregard that is vbscript. The code works the same in VBA. You'll need to add a reference in your project for vbscript regular expressions v 5.5 .

 

[Ste4en]

I am an Access dummy. Is there an easier way or can you point me right on how to use that code.

thanks

 

[Ste4en]

Anyone else have any ideas. I heard that the split function for Access 2000 might help break a string down into an array of words from which I can compare from to another - would this work how do do I do this.

Thanks

 

[mousie]

With the help of Moniker he put me into this direction:

Open up Tools => Macros => Visual Basic editor and paste into a new module.

After pasting, use "Comparison: str_perc(S1Master,S1Submitted)" into your query

Public Function str_Perc(txtString1 As String, txtString2 As String) As Single

Dim str1Len As Integer
Dim str2Len As Integer
Dim ctr As Integer
Dim strLenMax As Integer
Dim NumMatches As Single

str1Len = Len(txtString1)
str2Len = Len(txtString2)
strLenMax = Switch(str2Len <= str1Len, str1Len, True, str2Len)

For ctr = 1 To strLenMax
NumMatches = NumMatches + Switch(Mid(txtString1, ctr, 1) = Mid(txtString2, ctr, 1), 1, True, 0)
Next

str_Perc = NumMatches / strLenMax * 100

End Function