Search for similar string (1 Viewer)

[mkaeser]

Hello all,

I am trying to write some code to search for similar strings. I am creating a database with records that all contain street addresses. These addresses may have more than one record attached to it, and we would like for folders to be created containing the records with similar street addresses. Problem is, all the existing records are from an excel spreadsheet that did not contain any data validation, so there are several instances of:
123 Street
123 st
123 street job 1
123 st job 2
etc....

So I am trying to write code to prevent this from happening in the future, by searching the database for a similar street address and asking the user if this is the address they are trying to enter. I have been trying to do this with the DLookUp function, as such:

Private Sub ProjectName_AfterUpdate()
Dim stLink, pName As String

pName = Me.ProjectName

stLink = DLookup("[ProjectName]", "tblMaster", "[ProjectName] LIKE '" & pName & "%'")
If IsNull(stLink) Or stLink = "" Then
Exit Sub
Else
MsgBox "There is a past project with this name"
Me.ProjectName = stLink
End If
End Sub

I have worded the stLink line different ways, and have used (*) instead of (%) but nothing is working. The CODE is working, as in no errors, but it is not finding a similar project that I know is present. Any ideas? Thank you!

 

[GohDiamond]

Search this forum for "autocomplete: there are several threads
Cheers
Goh
Like this one: http://www.access-programmers.co.uk/forums/showthread.php?t=264757

 

[mkaeser]

Thank you for the information, I'm not sure if this will help with my situation since this is a data entry form and its not a combo or list box with values, but I will see if the ideas in that link provide some further insight, thanks!

 

[Galaxiom]

 "[ProjectName] LIKE '*" & pName & "*'")

 

[mkaeser]

Thanks Galaxiom, but that doesn't seem to be working for my needs. Right now, I am testing that code with input 1650 E. Clark, and it is saying there are no results when there is a record named 1650 E. Clarke.....

 

[Galaxiom]

It should work.

The only thing I can think of is that one of the characters is not what it seems. Is it possible that the E is actually a Cyrillic Ye?

 

[Brianwarnock]

Years ago when comparing names and addresses on two DBs I found that a fairly major problem was extra spaces, we cannot see them but the computer does, I had to create spaceless strings to do the comparison on.

Brian

 

[mkaeser]

That code will find a similar string if the variance is at the end, like such:
Entered text: 123 St
Similar string found: 123 Street

But for difference in the middle of the string, such as:
Entered text: 123 East Street
Similar string: 123 Easy Street
The code passes over that similar sting, which is the exact opposite of what I need it to do haha.

Perhaps I did it wrong?

stLink = DLookup("[ProjectName]", "tblMaster", "[ProjectName] LIKE '*" & pName & "*'")

 

[mkaeser]

That sounds like quite the cumbersome task Brianwarnock!

 

[Galaxiom]

But for difference in the middle of the string, such as:
Entered text: 123 East Street
Similar string: 123 Easy Street
The code passes over that similar sting, which is the exact opposite of what I need it to do haha.

The Like comparison will only find a string within the string. Since the search string is not in the data of course it won't show up.

What you need is the Damerau-Levenshtein function. Use the function in post 10.

 

[Galaxiom]

Years ago when comparing names and addresses on two DBs I found that a fairly major problem was extra spaces, we cannot see them but the computer does, I had to create spaceless strings to do the comparison on.

First thing I do with data is eliminate that problem by replacing all double spaces with single spaces.

 

[Brianwarnock]

First thing I do with data is eliminate that problem by replacing all double spaces with single spaces.

If doing complete comparisons you need to trim front and rear too, as the data in putter can sometimes add a space before or after typing, this may not apply doing what the poster is doing.

Brian

 

[BlueIshDan]

DLookUp returns a number that represents the number of records that match.

-- WITHOUT TRIM ---

Also, if you want an exact match don't use LIKE, just use:

	If  DLookup("[ProjectName]", "tblMaster", "[ProjectName] = '" & pName & "'") > 0 Then 
		MsgBox " exists "
	Else: .. does not exist code ..
	End If

If you want anything that contains that project name (not suggested):

	If DLookup("[ProjectName]", "tblMaster", "[ProjectName] LIKE '*" & pName & "*'") > 0 Then 
		MsgBox " exists "
	Else: .. does not exist code ..
	End If

--- WITH TRIM ---
If you want an exact match don't use LIKE, just use:

	If  DLookup("[ProjectName]", "tblMaster", "[ProjectName] = '" & Trim(pName) & "'") > 0 Then 
		MsgBox " exists "
	Else: .. does not exist code ..
	End If

If you want anything that contains that project name (not suggested):

	If DLookup("[ProjectName]", "tblMaster", "[ProjectName] LIKE '*" & Trim(pName) & "*'") > 0 Then 
		MsgBox " exists "
	Else: .. does not exist code ..
	End If

 

[Galaxiom]

DLookUp returns a number that represents the number of records that match.

No it doesn't. It returns the expression in the first argument.

You are thinking of DCount.

 

[BlueIshDan]

No it doesn't. It returns the expression in the first argument.

You are thinking of DCount.

Isn't that embarrassing hahahahaha!

 

[BlueIshDan]

What if you were to write a function that was passed the a string to compare to all other streets and also a number that represents word matches. This function would return an array of string results that contain X number of the same words.

Would this help you?
Give me your table name and field names and I can write this for you.

 

[BlueIshDan]

Try this function:

Private Function FindSimilarProjects(ByVal project_name As String, ByVal delimiter As String, ByVal per_cent As Integer) As String()
    
    Dim results() As String
    Dim results_count As Long: results_count = -1
    Dim rs As Recordset: Set rs = CurrentDb.OpenRecordset("tblMaster")
    
    Dim matches As Integer
    Dim rs_word_count As Integer
    
    With rs

        While Not .EOF
        
            matches = 0
            rs_word_count = 0
            
            For Each var_rs_split In Split(!ProjectName, delimiter)
            
                rs_word_count = rs_word_count + 1
                
                For Each var_split In Split(project_name, delimiter)
                
                    If var_rs_split = var_split Then
                    
                        matches = matches + 1
                        Exit For
                        
                    End If
                    
                Next
                
            Next
            
            If matches >= (rs_word_count * (per_cent / 100)) Then
                results_count = results_count + 1
                ReDim Preserve results(results_count)
                results(results_count) = !ProjectName
            End If
            
            rs.MoveNext

        Wend
        
    End With
    
    FindSimilarProjects = results
        
End Function

    Dim msg As String
    For Each var_project_name In FindSimilarProjects(pName, " ", 25)
        msg = msg & var_project_name & vbNewLine
    Next
    MsgBox msg

 

[mkaeser]

Wow that code seems to be more than I need Galaxiom, I don't even know where to start! I will look more into it and see how it works, thanks!

 

[Brianwarnock]

Wow that code seems to be more than I need Galaxiom, I don't even know where to start! I will look more into it and see how it works, thanks!

Dan will be really blue if you credit others with his work.

Brian

 

[BlueIshDan]

Wow that code seems to be more than I need Galaxiom, I don't even know where to start! I will look more into it and see how it works, thanks!

Haha
Your welcome I guess.