help with (sort of) a search engine in forms

[mtimbol]

Hi

I have a table with fields names such as Company, products, etc.

I need help on how to make search engine to retrieve records in my products
column.

I was planning to make an unbound text box with a command button beside it.
The user enters the keyword in the text box and the command button opens a
query or table which lists all records containing the keyword.

I tried to make one using a query with a criteria parameter referring to my unbound text box
like:

Field: Products
Table: Companies
Sort:
Show:
Criteria: [Form]![Search Form]![Unbound TxtBox Name]

This works fine as long as you know the exact record entry in the table but doesn't return
anything if the word entered lacks even just one character.

I tried using Like operator with an asterisk at the start and end using the parameter above but it doesnt work.

MLT

 

[RickSpr]

There is nothing built into Access that supports approximate matching, except for the limited Like operator, which won't work for this. But there is a technique called "soundex" matching that finds phonetically similar words. It was originally developed to accomodate variations in spellings of personal names.

Each consonant is assigned a numerical value, and a word's soundex encoding consists of the first letter and a number derived from the numerical values of the consonants.

To apply it, you'd compute a soundex value for each product name, and store it in the table with the product info. On your search form, you'd take the value entered by the user and compute it's soundex value, and use that to look up a matching product by its soundex.

Unfortunately, I don't have any information about the function that computes the soundex value. Maybe you could find it in an old book on algorithms. It was used a lot on mainframes 15-20 years ago. (It may still be used for all I know.) Rick Sprague

 

[PaulF]

how did you format your Like statement?

should have been

Like "*" & [Form]![Search Form]![Unbound TxtBox Name] & "*"


PaulF

 

[mtimbol]

Thanks guys. Glad to have people like you give tips to newbies like me.