Search String to include variants

[Denise2020]

I have a search string connected to a text field on a form and it searches many of the controls on the form and works very nicely. It is on another computer so I cannot copy and paste it here easily but it is in the form of

strSearch = "SELECT * from qry where ((objID like ""*" & strText & "*"") OR... and continues to include all of the controls I want to be included in the search.

Is there a way to search that would include partial matches? Example: search for Object-7, returns not only Object-7 but also Object7 and Object 7, and ideally even Obj7, Obj-7, and Obj 7?

Thanks!

 

[ebs17]

You are already using a pattern search.
So you just have to design the pattern a little differently to get all the hits you want. In the example...

where objID like "*Obj*7*" OR ...

Means
strText = "Object-7"
strText = "Obj*7"

 

[plog]

Is there a way to search that would include partial matches? Example: search for Object-7, returns not only Object-7 but also Object7 and Object 7, and ideally even Obj7, Obj-7, and Obj 7?

Yes, a custom function. An Access database has no way to know that when given the literal string 'Object-7' it should somehow truncate characters from the middle of it ('ect-') and also search for 'Obj7'. It just doesn't understand our language enough to make that leap. So, to achieve what you want, exactly as you have it set up you would need to write (or find or hope someone provides) a fuzzy search function. You would give it a string and it would score how much of a match it is with another string, then you set the threshold (e.g. above 65% match) and include those results in the query.

Another simpler option is to reconfigure your search method. Instead of 1 text input, provide a few. That way you can search for 'Obj' and '7' and return any record that has both/either.

 

[Denise2020]

You are already using a pattern search.
So you just have to design the pattern a little differently to get all the hits you want. In the example...

where objID like "*Obj*7*" OR ...

Means
strText = "Object-7"
strText = "Obj*7"

If I am using a text field on a form for users to be able to search in, are you saying I have to tell them to use the wildcard *? (we have another Sharepoint site where we have to use a wildcard search and I can say most users have a very difficult time with that still. I was hoping for a smarter search function that could eliminate/reduce "user issues". Or is there a way to build in the variants with * * * * within the code itself? Thanks very much for your reply!

 

[Denise2020]

Yes, a custom function. An Access database has no way to know that when given the literal string 'Object-7' it should somehow truncate characters from the middle of it ('ect-') and also search for 'Obj7'. It just doesn't understand our language enough to make that leap. So, to achieve what you want, exactly as you have it set up you would need to write (or find or hope someone provides) a fuzzy search function. You would give it a string and it would score how much of a match it is with another string, then you set the threshold (e.g. above 65% match) and include those results in the query.

Another simpler option is to reconfigure your search method. Instead of 1 text input, provide a few. That way you can search for 'Obj' and '7' and return any record that has both/either.

Exactly, I realize what you are saying. A "fuzzy search function" was what I was after. Any tips on how that is achieved or where to look? I have been actually trying to solve this bit of a problem for a few years on and off without success (work on it a few days and give up again in defeat). Thanks for your reply!

 

[ebs17]

The following post is fairly current.

First of all, I would like to add that the strength of databases / SQL is the processing of standardized content. One point of such standardization is the storage of atomic information.

The more arbitrary you allow the content to be, the more complicated it becomes to process it later. A lack of cleverness in data and structures then requires smarter processing.

 

[arnelgp]

Soundex will only search the first 4 characters on your search string and will show you results, most likely, the same sound.