Allow Like Operator To Include Results With Differing Punctuation

[Tim Mullin]

Hello Everyone,

I'm having some trouble with a query I'm trying to run and I cannot find any previous cases with solid solutions online...hoping someone could help!

Every month we receive a commission statement from a company we work with that has a list of names on it, along with other details regarding the commission being paid. We have a database in place that helps us keep track of these commissions and we want to implement a new feature that allows us to copy and paste data from the commission statements into a template and upload it into the database, and then run a query that matches the names from the statement(TableB) to the names in the database(TableA) so it can pull other relevant info from the database end.

The only issue is that the names don't always match up - either the name on the commission statement will only be a portion of what we have it stored under in the database or there will be a comma or period missing on one end. (i.e. in the database we have "Company A, LLC and the commission statement will have Company A LLC) I've set up a "Like" operator in the names column of the query that successfully returns results that partially match, however when punctuation is involved (such as my example) it will not return it. I'm not the greatest at coding or access but was wondering if there was a simple solution to this?

Current Like operator in the query is:
Field: Name
Table: TableA
Like "*" & [TableB].[Name] & "*"

Please let me know!

thanks,
Tim

[zmbd]

So to get a good starting point:
- You are able to consistently get: "Company A, LLC" or "Company A LLC" into your database
- the question is then being able to compare "Company A, LLC" to "Company A LLC" and recognize that these are the name company?
- Do you want to ignore all non-alphanumeric information in the company names (i.e. Company A, LLC == Company A - LLC == Company A$ LLC == Company A: LLC) or just specific characters such as the comma
- To do this for each company as listed Between the statement(TableB) to the names in the database(TableA)

Is this correct?
Or are you haveing issues getting the data into TableB too?

-z

[Tim Mullin]

How you described it is exactly correct - you even went a step further to something I didn't think about. I would like the query to recognize the varying formats from TableA and TableB as the same company, and if we could set it up to ignore all non-alphanumeric information that would be outstanding. (In theory this would solve the problem)

TableB is populated by by the "raw" data from the commission statements we receive that is imported via an excel template, so no issues there.

[zmbd]

Hello again...
You should be able to do this by changing the pattern matching format.
http://office.microsoft.com/en-us/ac...001032253.aspx

http://msdn.microsoft.com/en-us/libr...ffice.12).aspx

You may need to use a vba code too...

Some Gremlins have moved into one of my satellite labs so I need to pop over to to the other lab this afternoon so I'll take a closer look at the pattern in a little while

However, Rabbit was just working on something just like this so He may hop in with the pattern before I do

-z

[Rabbit]

The problem with the LIKE operator in Access and SQL Server is that they are limited compared to regular expressions. And unfortunately, neither one has a regex implementation built into the SQL engine.

I'm afraid VBA is the only choice. Unless you don't mind casting a wide net. In which case you can replace all spaces in the clean table (tableB) with %.

[Tim Mullin]

hmmm..I gave it a few tries but couldn't get anything to work.

I first tried to use the "not a digit" ([!0-9]) pattern but it kept giving me an error saying it was invalid bracket name. The code looked like this:

Like "*" & [TableB].[Name] & "*" And Like [!0-9]

Even if this did work it still wouldn't solve the issue fully, as some of the "Names" have numbers in them. I tried figuring out a combination of the patterns to have it work but still no luck, as I want to return results that have punctuation, however ignore it while looking for matches. This must be possible! Thanks for your help so far!

[Tim Mullin]

My response was to zmbd's post...Just saw your Rabbit - using VBA would it be possible to get it to work? I'm open to spending a fair amount of time on this as it would save us a whole lot more in the long run...

[zmbd]

@Tim Mullin
Here's a function one that strips all non-numerics from the string. Even if we don't use it directly, we'll more than likely use the update query this talks about to clean up your table.
http://support.microsoft.com/kb/210537

We should be able to modify the code... however, I think that the replace method might be easier. I'll play with that in a few momements after I get the next analysis started. :)
-z

[zmbd]

Try the following:

I made a table called tblNamesDirty
[dirtyPK] autonumber (just because I like PK)
[DirtyNames] text(50)

Then I have the following function:

Option Compare Database
Option Explicit
 
 
Function RemoveNonAlphas(ByVal varDirty As Variant)
Dim strCleaned As String
Dim intPosition As Integer
Dim strCurrentLetter As String
'
'error trap
On Error GoTo z_errortrap
'
'start at the begining
intPosition = 1
If IsNull(varDirty) Then
    RemoveNonAlphas = "Is_Null_Value"
Else
    For intPosition = 1 To Len(varDirty)
        strCurrentLetter = Mid(varDirty, intPosition, 1)
        Select Case strCurrentLetter 
'You could use a Ucase(strCurrentLetter)
'and then just compare on the uppercase letters
'however, I wanted to show that you could use ranges
'of charactors.
'
            Case "A" To "Z", "a" To "z"
                strCleaned = strCleaned + strCurrentLetter
            Case " "
'I left the spaces in the string; however, I didn't
'want two spaces in a row, so I purge one if needed.
                If Not Right(strCleaned, 1) = " " Then
                    strCleaned = strCleaned + strCurrentLetter
                End If
        End Select
    Next intPosition
   RemoveNonAlphas = strCleaned
End If
Exit Function
z_errortrap:
RemoveNonAlphas = "#Error"
End Function 

I use this in a query such that

SELECT tblNamesDirty.[DirtyNames],
   RemoveNonAlphas([DirtyNames]) AS [CleanName]
FROM tblNamesDirty;

In this case, when you run the query, there will be the column with the DirtyNames and then a column with the CleanName.

I used the select-case in this method because you can work with the data without a whole lot of grief.

Now you should be able to compare your two tables on the [CleanName]

(( You could also run this as an update query and clean the inbound table; however, I don't think that you'll need to do this...))

-z

[Rabbit]

In addition to removing non alphanumeric characters, you may be interested in fuzzy matching:
Levenshtein Edit Distance Algorithm
NGram Approximate String Matching Algorithm