Like Operator To Find Any Similarities

[ahd2008]

Good day everyone
I am trying to use LIKE operator in a query to find any similarities for a word entered into a parameter. To make my idea clearer, let me give you this example:
Assume that I have a table called tblEmpDepts and there are the following depts.:
1- Finance
2- IT
3- Maintains

If the user entered “Fin” or “Finan”, they query should retrieve record number#1 which is Finance. I understand that this could be accomplished by enclosing “*” between query parameter:

Like “*” & [Parameter] & “*”

However, I can’t seem to have the query retrieve the first record “Finance” when the user enters additional letter such as “Finances” or “Financee”.
Any idea would be highly appreciated
Thanks

[Stewart Ross]

The 'Like' operator is working correctly in the circumstances you describe. If the phrase used as a comparator (FINANCES, say) is longer than the phrase you are comparing then the two cannot match, because the sequence of letters in the comparator phrase does not occur in the one you are testing.

The word FINANCE does not contain the letters FINANCES, so LIKE will not return TRUE for this comparison.

It is possible to restrict the length of the comparator so that it is never longer than the field you are comparing it to. If I assume that the name of the field in table tblEmpDepts is Dept, it can be done like this:

Dept like "*" & Mid([Parameter], 1, Len([Dept])) & "*"

It is better practice that you do not rely on users typing in department names - why not just get the user to select the department using a drop-down list on your form instead. That way the user cannot make a mistake at all.

-Stewart

[Rabbit]

If you want 'FINANCEEEEEEES ' to equal 'FINANCE' you are moving beyond simple character comparison and into fuzzy string matching. There is no native capability in Access to do this. You would have to implement your own functions in VBA.

Some algorithms you may want to look at are SOUNDEX, METAPHONE and its variants, and LEVENSHTEIN. The first two attempt to encode the string into a representation of how a word would sound. And the last one is a measure of edit distance between the two words. Because you would be using fuzzy string matching, you may get false positives. For example, you may find that 'FINANCEEEEEES' also equals 'FINLAND'.

[NeoPa]

I'm pleased that Rabbit has added that information, as the subject is somewhat incomplete without it. However, you should be very sure that this is exactly what you need (rather than something nice to have), I would suggest, before committing yourself to such an approach. It is not mainstream and won't be very easy for someone without a fair bit of experience and logic/coding skills.

It's worth including because there will certainly be people out there for whom this is necessary and so will be the correct approach (for them).

[ahd2008]

Thanks to you Stewart Ross and Rabbit for the clarification. I understand what you are saying. Thanks again

wish you all the best for the time and efforts you dedicate in helping us