By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,472 Members | 2,195 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,472 IT Pros & Developers. It's quick & easy.

Allow Like Operator To Include Results With Differing Punctuation

P: 48
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
Aug 16 '12 #1
Share this Question
Share on Google+
9 Replies


zmbd
Expert Mod 5K+
P: 5,287
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
Aug 16 '12 #2

P: 48
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.
Aug 16 '12 #3

zmbd
Expert Mod 5K+
P: 5,287
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
Aug 16 '12 #4

Rabbit
Expert Mod 10K+
P: 12,315
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 %.
Aug 16 '12 #5

P: 48
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!
Aug 16 '12 #6

P: 48
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...
Aug 16 '12 #7

zmbd
Expert Mod 5K+
P: 5,287
@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
Aug 16 '12 #8

zmbd
Expert Mod 5K+
P: 5,287
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:
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4.  
  5. Function RemoveNonAlphas(ByVal varDirty As Variant)
  6. Dim strCleaned As String
  7. Dim intPosition As Integer
  8. Dim strCurrentLetter As String
  9. '
  10. 'error trap
  11. On Error GoTo z_errortrap
  12. '
  13. 'start at the begining
  14. intPosition = 1
  15. If IsNull(varDirty) Then
  16.     RemoveNonAlphas = "Is_Null_Value"
  17. Else
  18.     For intPosition = 1 To Len(varDirty)
  19.         strCurrentLetter = Mid(varDirty, intPosition, 1)
  20.         Select Case strCurrentLetter 
  21. 'You could use a Ucase(strCurrentLetter)
  22. 'and then just compare on the uppercase letters
  23. 'however, I wanted to show that you could use ranges
  24. 'of charactors.
  25. '
  26.             Case "A" To "Z", "a" To "z"
  27.                 strCleaned = strCleaned + strCurrentLetter
  28.             Case " "
  29. 'I left the spaces in the string; however, I didn't
  30. 'want two spaces in a row, so I purge one if needed.
  31.                 If Not Right(strCleaned, 1) = " " Then
  32.                     strCleaned = strCleaned + strCurrentLetter
  33.                 End If
  34.         End Select
  35.     Next intPosition
  36.    RemoveNonAlphas = strCleaned
  37. End If
  38. Exit Function
  39. z_errortrap:
  40. RemoveNonAlphas = "#Error"
  41. End Function 
I use this in a query such that
Expand|Select|Wrap|Line Numbers
  1. SELECT tblNamesDirty.[DirtyNames],
  2.    RemoveNonAlphas([DirtyNames]) AS [CleanName]
  3. 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
Aug 16 '12 #9

Rabbit
Expert Mod 10K+
P: 12,315
In addition to removing non alphanumeric characters, you may be interested in fuzzy matching:
Levenshtein Edit Distance Algorithm
NGram Approximate String Matching Algorithm
Aug 17 '12 #10

Post your reply

Sign in to post your reply or Sign up for a free account.