User Entered Wildcard Search

[Cotswold]

Whilst I can specify a specific pattern for a Wildcard search using LIKE, I need to allow the user to use a Wildcard entry of WORD*WORD or *WORD*WORD* in a Query.

As a For-Instance; in bank imports NS&I can be referred to as NS&I, NS+I, or NS +I and from the imports what I want is to use are NS?I or NS*I in a LIKE statement. (NS&I is a British government quango. So the use of standardisation, consistency or logic are all of no moment to them)

In the CRITERIA of a Query I can use LIKE "*" & [FilterWord] & "*" or LIKE [FilterWord] & "*" which both work as expected.

If the user then enters :
*NS* or NS* then I can sort the Query to do the filters correctly. However, the first will include a pile of stuff like TRANSAC2ABC and Morrisons as well. The second will include everything starting with NS and followed by anything. Whilst the 2nd will (in this example) filter out just NS&I, NS+I, and NS +I. It will not be a universal solution for other searches as it could draw a huge amount of unwanted data into the filter. rendering it useless. Mainly because it doesn't give me the option to restrict the search after the last wildcard, by allowing the user to specify a word or character after the 2nd wildcard *. The theory being that a user will know a first section, the middle may change, or is something they are unsure of, but they know one or two characters at the end of the word to filter.

What I would like to do is allow the user to enter text as *ABC*XYZ or ABC*XYZ and then I thought I would be able to execute the query as follows:
LIKE "*" & [1stPartFilterWord] & "*" & [2ndPartFilterWord] or LIKE [1stPartFilterWord] & "*" & [2ndPartFilterWord] but neither syntax works at all.

I cannot find anything on the internet on User Entered Wildcards for Queries in MS Access. My question is does anyone know the correct syntax?

 

[arnelgp]

you can replace * with [*]

dim sFilter1 as string
sFilter1 = replace$([1stPartFilterWord], "*", "[*]")

 

[jdraw]

If you want a more universal solution, then I think you will need to provide more examples to help readers understand the scope of search patterns and expectations.

This is the reality of wildcard usage.

However, the first will include a pile of stuff like TRANSAC2ABC and Morrisons as well. The second will include everything starting with NS and followed by anything.

You will need additional criteria, but you may find some patterns to refine searches.

 

[CJ_London]

I train users to use the * wildcard when necessary. I usually include the final * and use a * as a separator for AND and a / as a separator for OR

basic criteria is

criteria= "myfield Like '" & searchStr & "*'"


User types 'NS' (they usually know what it starts with)
they get everything starting with NS

User types '*NS'
they get everything containing NS

User types 'NS*P'
they get everything starting with NS and with a subsequent P

User types '*NS*P'
they get everything containing NS and with a subsequent P

For the or's it is necessary to split the searchstr

dim i as integer
dim sa() as string

if instr(searchstr,"/") then 'multiple criteria
    sa=split(searchstr,"/")
    criteria=""
    for i=0 to ubound(sa)-1
        criteria =criteria & " OR myfield like '" & sa(i) & "*'"
    next i
    criteria=mid(criteria,5)
else
    criteria= "myfield Like '" & searchStr & "*'"
end if

 

[MajP]

If this was me I am building a form to help me create complex filter strings. I would start with a table of search types
This is a start but could add other types of searches besides like and lengths. Obviously need to add NOT.
SearchType SearchType

SearchTypeID	Type
1​	Contains (*text*)
2​	At Beginning (text*)
3​	At End (*text)
4​	Min Length
5​	Max Length
6​	Length

Then I would have a table where I can select the Text and decide what type of search and if it is an and or requirement.
With some code based on the tables and selections can build a very complex string.

In this case can determin if NS is contained, at beginning, or end

There was several thousands records but only a few meet NS at beginning and I at end.
However I do have some with text then NS & I

In this case I can set the max string length for example but must contain NS then an I.

 

[Cotswold]

I train users to use the * wildcard when necessary. I usually include the final * and use a * as a separator for AND and a / as a separator for OR

basic criteria is

criteria= "myfield Like '" & searchStr & "*'"


User types 'NS' (they usually know what it starts with)
they get everything starting with NS

User types '*NS'
they get everything containing NS

User types 'NS*P'
they get everything starting with NS and with a subsequent P

User types '*NS*P'
they get everything containing NS and with a subsequent P

For the or's it is necessary to split the searchstr

dim i as integer
dim sa() as string

if instr(searchstr,"/") then 'multiple criteria
    sa=split(searchstr,"/")
    criteria=""
    for i=0 to ubound(sa)-1
        criteria =criteria & " OR myfield like '" & sa(i) & "*'"
    next i
    criteria=mid(criteria,5)
else
    criteria= "myfield Like '" & searchStr & "*'"
end if

Many thanks CJL that works perfectly.
I must admit that I would never have thought that a user entering the * between the search field and its suffix would be the solution. The *NI*I pulls a couple of unwanted records out of 3,500 but otherwise fine. And like you say I can always look into splitting it. Entering NS*I pulls exactly the correct records.

 

[MajP]

So I added a NOT field

But you can see a lot of values end in Li, mi, di, ski. So I can exclude those easily

 

[CJ_London]

actually code can be simplified, and the -1 should not be there!
all that is required is

dim i as integer
dim sa() as string

    sa=split(searchstr,"/")
    criteria=""
    for i=0 to ubound(sa)
        criteria =criteria & " OR myfield like '" & sa(i) & "*'"
    next i
    criteria=mid(criteria,5)

 

[Cotswold]

So I added a NOT field
View attachment 103115
But you can see a lot of values end in Li, mi, di, ski. So I can exclude those easily
View attachment 103116

Many thanks for your suggestions MajP, but maybe a bit too much work for what is required.
These are just random searches someone could do through their banking records. They may need to pull everything for (say) NS&I or for just one supplier from last year or years ago, or to lookup what was spent last time. Most of the filters are done using the cost allocation which is given to each imported bank entry and the filters are often date related. How much did we give young Jimmy for his birthday last year? What is the total of Gas & Electric this year?, last year? What was spent on motoring last year?................etc.

Your way of looking at it is certainly food for thought though.

 

[MajP]

The idea cam from this thread

Solved - Search all words in field against all words in another field

I kind of disagree in a db because you can probably build a much better search using sql Like search. This is based on a pretty thorough demonstration of a couple of interfaces using (Soundex, Simil, Levenshtein, and just find as you type) Like?? Like won't find similarities in strings with...

www.access-programmers.co.uk

Which demoed a way to quickly search for one or more key words very quickly and determine if it "must" be included or "can" be included.

Here is the demo for the idea described in this thread along with the keyword search demo.