Searching for a Like String in Another Table - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Searching for a Like String in Another Table

Richard Pitassy

8 months ago

In our retail golf store, customers often ask us to let them know if we get in a particular club they are looing for. Moving away from our previous post-it note system I created a table named [tblBolo] where the user enters a description [BoloDesc] of what customer wants, name, telephone, etc.

The db has another table named [tblProducts] where the user enters data on incoming products including [ProdDescription].

I am trying to write a procedure in the After Update event of [tblProducts].[ProdDescription] that will notify the user if any part of [ProdDescription] matches any part of [tblBolo].[BoloDesc].

After a couple of weeks of effort (including AI) I have not been able to come up with a solution that works in the same way as the "Like" operator with wildcards works in a query that compares the entire string for a match. Any suggestions on how to approach this task would be greatly appreciated.

Many thanks!

Adam Schwanz @Reply

8 months ago

You're going to struggle to find the happy medium here with if you use exact spelling or just close, and you're going to have some words that don't really matter if they match or not. For instance I have 2 fields on my form, FieldA and FieldB. When I push the button it runs this code.

  Dim str1 As String, str2 As String

    Dim arr1() As String, arr2() As String

    Dim w1 As Variant, w2 As Variant

    Dim matchList As String

    

    ' Get values from form fields

    str1 = Nz(Me.FieldA, "")

    str2 = Nz(Me.FieldB, "")

    

    ' Split into words

    arr1 = Split(str1, " ")

    arr2 = Split(str2, " ")

    

    matchList = ""

    

    ' Compare each word (partial match allowed)

    For Each w1 In arr1

        For Each w2 In arr2

            If InStr(1, LCase(Trim(w1)), LCase(Trim(w2))) > 0 _

               Or InStr(1, LCase(Trim(w2)), LCase(Trim(w1))) > 0 Then

               

                ' Avoid duplicates

                If InStr(1, matchList, w1, vbTextCompare) = 0 And _

                   InStr(1, matchList, w2, vbTextCompare) = 0 Then

                    matchList = matchList & w1 & ", "

                End If

            End If

        Next w2

    Next w1

    

    If matchList <> "" Then

        matchList = Left(matchList, Len(matchList) - 2)

        MsgBox "Similar words found: " & matchList, vbInformation

    Else

        MsgBox "No similar words found.", vbExclamation

    End If

My two test sentences in FieldA and FieldB are
"The hydraulic pump failed during operation, causing oil leakage and vibration across the main shaft."
"Engine vibration was detected near the pump housing, leading to oil leakage and reduced performance."

This would return: Similar words found: "The, pump, oil, leakage, and, vibration
This is an example of "close" or "like". It's also possible to only do exact match, but then "car" doesn't match with "cars" kind of thing, so you miss out on stuff then too. Pros and Cons to both sides.

Raymond Spornhauer @Reply

8 months ago

Richard

I would create a new Many-Many relationship between the customer follow-ups and the products they are looking for, then create a report that shows the products that are in stock for that customer.

You could add a "follow-up" yes/no check mark to use as a filter.

Then you could do a search for any and all products that meet the description, add it to your follow up table, then when it's in stock, it will show up on your daily report.

-Raymond

	Matt Hall @Reply 8 months ago
This is probably a naive question from a non-golfer but if you have objective description fields for the club like [brand], [model], [club], and [length] would knowing that the club is a Ping, G440, 7-iron, 37" be enough to filter down to the clubs of interest?

	Richard Pitassy OP @Reply 8 months ago
Thanks fellas - I will work on figuring out your ideas and see what I can come up with. Matt, the answer to your question is "Yes".

	Matt Hall @Reply 8 months ago
If you have a tblBrand, tblModel, tblClub and tblLength, you can store the ID for each of these as a foreign key in the record for each club. The Big Button Form Series uses an underlying structure that should work pretty well. You can then search or filter based on any combination of these ID's. Additionally, it eliminates typo's during data entry and lookup.

	Richard Pitassy OP @Reply 8 months ago
Thanks Matt. I'll take a look at that.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View